adr: 0010 title: Phone number policy — format check + UNIQUE constraint, no SMS OTP status: accepted implementation-status: implemented date: 2026-05-22 deciders: [@amalkrsihna] affects-specs: [signup-and-onboarding] affects-code: - ftl-backend/internal/user/service.go # regex + new unique-violation handling (~L70, L228) - ftl-backend/migrations/0XXX_add_phone_unique_index.up.sql # NEW migration - ftl-backend/migrations/0XXX_add_phone_unique_index.down.sql # NEW migration (rollback) supersedes: null superseded-by: null

ADR-0010: Phone number policy — format + uniqueness, no SMS OTP¶

Pending implementation. Defines the anti-abuse posture on phone numbers. Today the phone is regex-validated but not unique — one phone can register N accounts. This ADR adds the DB constraint and explicitly rules out SMS OTP for cost + UX reasons.

Context¶

Today's phone handling:

Format validation (ftl-backend/internal/user/service.go:70):
```
phoneRegex = regexp.MustCompile(`^[6-9]\d{9}$`)
```
Indian mobile pattern — 10 digits, first digit 6-9. Applied at registration (service.go:228) and on profile update (service.go:402). Error string: "phone must be 10 digits starting with 6-9".

Schema (ftl-backend/migrations/000012_add_user_profile_fields.up.sql:1-2):

ALTER TABLE users ADD COLUMN IF NOT EXISTS phone TEXT DEFAULT '';
ALTER TABLE users ADD COLUMN IF NOT EXISTS age INTEGER DEFAULT 0;

The phone column is plain TEXT. No UNIQUE, no NOT NULL, no index of any kind.

No SMS infrastructure. The internal/otp package handles email OTP for the registration ticket (6-digit code, Redis-backed, 5-minute TTL, keyed by Google sub). It has no relation to phone numbers. There is no Twilio, no MSG91, no SMS package anywhere in internal/.

The gap: the same phone number can register an unbounded number of accounts. For a trading game where wallets carry real-money-adjacent value (referral bonuses, leaderboards, possibly future monetary integrations), the absence of any per-phone uniqueness signal is an abuse vector — a single bad actor can mint N accounts to farm referral bonuses, manipulate leaderboards, or amplify any other per-account incentive the product later adds.

The operator has decided that phone uniqueness is enough — full SMS-OTP verification is NOT in scope for the pre-launch window.

Decision¶

We will keep the format-only regex check, add a partial UNIQUE index on the phone column (allowing legacy empty rows to coexist), and explicitly defer SMS OTP to a future ADR if needed.

Concrete changes the paired code PR will encode:

Migration¶

Two new files, paired up/down:

ftl-backend/migrations/0XXX_add_phone_unique_index.up.sql:

-- Add a partial unique index on phone so duplicate non-empty values are rejected.
-- Empty strings (legacy default from migration 000012) are deliberately allowed —
-- a NULL/empty phone is "phone not collected" and should not collide.
--
-- CONCURRENTLY: required so the index build does not block the users table.
-- WHERE phone <> '': ensures the existing empty-phone rows do not collide.

CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS users_phone_unique
  ON users(phone)
  WHERE phone <> '';

ftl-backend/migrations/0XXX_add_phone_unique_index.down.sql:

DROP INDEX CONCURRENTLY IF EXISTS users_phone_unique;

The XXX number is the next-available migration number at land time (currently 0XX; check ls migrations/ immediately before adding).

Pre-migration check (operator must run before applying)¶

Before the migration is applied to staging or prod, the operator runs:

SELECT phone, COUNT(*) AS dup_count, ARRAY_AGG(id ORDER BY created_at) AS user_ids
FROM users
WHERE phone <> ''
GROUP BY phone
HAVING COUNT(*) > 1;

If any rows return, the migration will fail (the unique index build aborts on the first duplicate). Resolution options (operator decides per-row):

Keep the earliest account — set the duplicate-phone columns to '' on all but the earliest, then re-run the migration.
Soft-delete the duplicates — set deleted_at = NOW() on duplicates and adjust the index predicate to WHERE phone <> '' AND deleted_at IS NULL (only if a soft-delete pattern exists in the schema — confirm before changing the migration).

The check + resolution is added to the deployment runbook for this PR.

Error handling in the user service¶

service.go:RegisterFromGoogle and the profile-update handler currently return the format-regex error on bad input. Add a new branch for the Postgres unique_violation (SQLSTATE 23505) returned by the INSERT/UPDATE when the new index fires:

// Pseudocode — exact placement depends on the existing error handling shape.
if pgErr, ok := err.(*pgconn.PgError); ok && pgErr.Code == "23505" && pgErr.ConstraintName == "users_phone_unique" {
    return nil, errors.New("this phone number is already linked to another account")
}

Surface the error to the frontend with a 409 HTTP status (Conflict) so the form can render a phone-specific inline error rather than a generic "registration failed".

No SMS OTP¶

Explicitly NOT added in this branch. Reasoning captured in Alternatives below.

Frontend impact (paired PR)¶

Surface the new 409 error inline next to the phone field: "This phone number is already registered. Did you mean to sign in?" with a link to /auth/google.
No other frontend change. The existing format-validation pattern in Register.tsx is fine as-is.

Consequences¶

Positive¶

One phone, one account. Closes the abuse vector for referral farming, leaderboard manipulation, and any future per-account incentive without SMS infrastructure cost.
DB-enforced. The constraint is the authority — no race condition between application-layer SELECT-then-INSERT.
Backward-compatible. Legacy rows with empty phone are unaffected. The partial-index predicate WHERE phone <> '' ensures they do not collide.
Cheap. No per-message cost, no third-party dependency, no signup-flow latency.

Negative¶

Phones are not verified for reachability. A user can type any valid-format Indian mobile number including someone else's. This is the SMS-OTP gap; we accept it for the pre-launch window.
Existing duplicate phones (if any) block the migration. Mitigation: the pre-migration check above is mandatory in the runbook.
Users who change phone numbers (e.g. port to a new carrier, lose old SIM) can be locked out of the account if the new number was already used by someone else. Low probability but real. Mitigation: support contact + manual override (no automated flow in this ADR's scope).

Neutral / new obligations¶

The migration runbook (in ftl-docs/guides/PROD-CUTOVER-2026-04.md or its successor) must list the pre-migration dup-check + resolution steps.
Monitoring: add a basic metric counting unique_violation errors per minute so a spike (e.g. someone scripting attempted duplicates) is visible.
A future ADR may revisit SMS OTP if the threat model changes (e.g. real-money integration). When/if that happens, build on this ADR rather than replace it — the UNIQUE constraint and OTP are complementary, not alternatives.

Alternatives considered¶

Alternative A: Format + SMS OTP at registration¶

Add Twilio or MSG91 integration to send a 6-digit OTP to the user's phone before registration completes. Verify the OTP before persisting the account.

Rejected for the pre-launch window:

Cost. ~₹0.15-0.20 per SMS in India (Twilio India / MSG91 enterprise rates). At an optimistic 10k signups during pre-launch and 1.5 attempts per signup (typo retries), that is ₹2,250-₹4,500/month. Real money for a feature whose threat model is "moderate abuse-deterrent on a pre-launch teaser."
Funnel friction. Adds 30-60 seconds and an external-app round-trip (waiting for SMS, switching app, copying code). Drop-off in registration funnels at this exact step is well-documented (10-30% loss is typical).
Infra dependency. A Twilio outage breaks signup entirely. The UNIQUE-constraint approach has no external dependency.

If the threat model worsens (real-money integration, larger user base, identifiable abuse patterns), revisit. For pre-launch, UNIQUE is the right cost/benefit tradeoff.

Alternative B: `NOT NULL` constraint on `phone`¶

Force every account to carry a phone, no empty allowed.

Rejected. Would break the existing rows with phone = '' (legacy default from migration 000012). The migration would either fail or need a backfill step that demands a real phone from existing users at next login — disruptive and out of scope. The partial unique index achieves uniqueness without changing the column's nullability semantics.

Alternative C: Application-level uniqueness check (SELECT-then-INSERT)¶

Before INSERT, the user service queries SELECT 1 FROM users WHERE phone = $1 LIMIT 1. If a row exists, return the duplicate error. No DB constraint added.

Rejected. Race condition: two concurrent registrations with the same phone both pass the SELECT (no row exists yet for either), then both INSERT, and both succeed. The DB constraint is the correct authority because the INSERT itself is atomic.

Alternative D: Per-IP rate-limit on registration instead of per-phone¶

Block more than N registrations from the same IP in a window.

Rejected as a substitute (not as a complement). Per-IP rate limits are useful but easy to evade with VPNs, mobile hotspots, or distributed accounts. Phone uniqueness adds a different signal — one that's harder to fake casually (you need real-number access to register). Both can coexist; this ADR adds the phone signal; per-IP rate limiting is a separate concern that may be added later.

Note on emergency rollback¶

If the new index causes unforeseen issues in production (e.g. an unanticipated false-positive collision pattern), the rollback is the paired .down.sql migration:

DROP INDEX CONCURRENTLY IF EXISTS users_phone_unique;

This is safe: dropping the index restores today's behavior. No data loss. The unique_violation error branch in the user service is harmless once the index is gone (it will simply never fire). The frontend's 409 handler is also harmless dead-path until the index is re-added.