How a Failed CREATE INDEX CONCURRENTLY Left an Invalid Unique Index and Let 1,140 Duplicate Signups Through

03:14 UTC. A PagerDuty alert from the nightly billing reconciliation job: duplicate_customer_count: 46 (threshold: 5). Forty-six Stripe customers sharing an email address with another Stripe customer, all created in the last 24 hours. The signup form had a unique index on email. It had had one for eleven days.

the setup

Two and a half weeks earlier, someone had filed a real bug: Bob@Client.com and bob@client.com could both sign up and land as two separate accounts, because the existing unique index on users.email was a plain btree on the raw column, and Postgres treats those as distinct strings. The fix was a functional index on the lowercased value, built CONCURRENTLY so the migration wouldn't take a lock on a table with nine million rows during business hours.

CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS idx_users_lower_email
  ON users (lower(email));

ALTER TABLE users DROP CONSTRAINT users_email_key;

The migration ran in a deploy step wired through a small bash runner, not the ORM's own migration tool, because CONCURRENTLY can't execute inside a transaction block and the ORM wrapped every migration in one by default. The runner had a known flake from a previous, unrelated migration that occasionally timed out against a long autovacuum run and needed a manual retry, so someone had added || true to the invocation months earlier to stop it from failing the whole deploy on that specific timeout.

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f "$1" || true
echo "migration $1 applied"

That line meant one thing on the day it was added and something much worse eleven days ago: no migration in this runner could fail the deploy anymore, for any reason.

the scramble

The on-call engineer's first theory was a bot wave. A spike of automated signups hitting the form overnight was a known failure mode from a prior incident, and it fit a 46-count anomaly. WAF request volume and the Cloudflare bot score distribution for the signup endpoint both came back flat against the seven-day baseline. Not bots.

Second theory: a Stripe webhook replay creating duplicate customer objects on Stripe's side for the same underlying user, which had happened once before after a webhook endpoint redeploy. Stripe's event log for the affected customer IDs showed no replayed customer.created events, one event per customer, each tied to a distinct user_id in the app database. The duplication wasn't happening at the Stripe layer. It was upstream of it, which meant the users table itself.

the hunt

The query that should have returned nothing did not:

SELECT lower(email), count(*), array_agg(id) AS user_ids
FROM users
GROUP BY lower(email)
HAVING count(*) > 1
ORDER BY count(*) DESC
LIMIT 5;

     lower      | count |        user_ids
-----------------+-------+-------------------------
 ops@client.com  |     2 | {4471902, 8839215}
 j.reyes@acme.io |     3 | {5011834, 8840102, 8840771}
 ...

A unique index on lower(email) had existed since migration 047. This result shouldn't have been possible. Someone on the call said exactly that, and it was the right instinct to check, not dismiss:

SELECT indexrelid::regclass, indisvalid, indisready
FROM pg_index
WHERE indexrelid = 'idx_users_lower_email'::regclass;

       indexrelid        | indisvalid | indisready
--------------------------+------------+------------
 idx_users_lower_email    | f          | t

indisvalid = f. The index existed, had a name, showed up in \d users and in every ORM introspection query the team had run in the last two weeks without anyone reading the invalid flag, and enforced nothing. An invalid index is excluded from constraint checking and from the planner entirely. It's schema wallpaper.

the find

CREATE UNIQUE INDEX CONCURRENTLY builds in two passes and validates uniqueness against live data at the end of the second pass. On the night migration 047 ran, one row already violated the constraint it was about to enforce: ops@client.com and Ops@Client.com, the second one inserted four months earlier by a partner data import that predated the app's email-normalization-at-write logic. The build hit that collision during validation, raised ERROR: could not create unique index "idx_users_lower_email" — key value violates unique constraint, and left the index in exactly the half-built, invalid state Postgres's own documentation warns about for a failed concurrent build.

ON_ERROR_STOP=1 made psql exit non-zero on that error, correctly. The || true two lines later caught that exit code and discarded it. The deploy logged migration 047_lower_email_unique.sql applied and moved on, because the echo ran unconditionally regardless of what happened above it. Nobody watching the deploy pipeline saw a failure, because there wasn't one, by the only definition the pipeline was checking.

Every signup after that point relied on the invalid index as its safety net against a plain, ordinary UI race: a slow network on mobile causing a form retry, a double-tapped submit button. For eleven days, that race resolved in favor of duplicate rows instead of the constraint violation everyone assumed would stop it.

the fix

First, the data. The legacy import row that broke the original build had to be resolved before any index could go valid, and the eleven days of duplicates created since then had to be merged, not deleted, since some carried real billing history:

WITH ranked AS (
  SELECT id, lower(email) AS norm_email,
         row_number() OVER (PARTITION BY lower(email) ORDER BY created_at ASC) AS rn
  FROM users
)
SELECT norm_email, array_agg(id ORDER BY rn) AS ids_oldest_first
FROM ranked
GROUP BY norm_email
HAVING count(*) > 1;

Each group went through a merge script that reassigned subscriptions and support tickets to the oldest account, then refunded and canceled the Stripe customer for every duplicate before deleting the row. Then the index itself: drop the invalid one and rebuild clean.

DROP INDEX CONCURRENTLY idx_users_lower_email;
CREATE UNIQUE INDEX CONCURRENTLY idx_users_lower_email ON users (lower(email));
ALTER TABLE users ADD CONSTRAINT users_lower_email_key
  UNIQUE USING INDEX idx_users_lower_email;

The || true came out of the migration runner entirely. In its place, an explicit check runs after every migration that touches an index, querying pg_index for anything left invalid, and it fails the deploy loudly if it finds one, which is the failure mode migration 047 should have produced in the first place:

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f "$1"
STATUS=$?
if [ $STATUS -ne 0 ]; then
  echo "migration $1 FAILED (exit $STATUS)"
  exit $STATUS
fi

INVALID=$(psql "$DATABASE_URL" -tAc \
  "SELECT count(*) FROM pg_index WHERE indisvalid = false")
if [ "$INVALID" -gt 0 ]; then
  echo "post-migration check: $INVALID invalid index(es) found"
  exit 1
fi

the aftermath

The other two invalid indexes were older, from migrations that had failed the same silent way months apart, on tables nobody was actively querying for duplicates, so nothing downstream had ever surfaced the gap. They're rebuilt now too.

The constraint had a name, a definition, and a spot in every migration log saying it applied cleanly. For eleven days, the only thing it actually did was look like a constraint.