# We Deleted 10 Real Users with a Test-Cleanup Script — RCA > Source: > Published: 2026-05-28 10:39:49+00:00 On **2026-05-11**, a test-cleanup script on [HoneyChat](https://honeychat.bot/) (Telegram-native AI companion, ~3 months in production, ~300 DAU, PostgreSQL 16 + Redis) ran: ``` DELETE FROM users WHERE id BETWEEN -91111200 AND -91111100; ``` About **ten real OAuth users** had IDs in that narrow window. They were now gone. Their `users` row, their `subscriptions` row, their `chat_sessions` / `web_messages` — all gone from Postgres, and recovery from backup was effectively impossible (more on that below). This is the postmortem and the contract we now run instead. The honest version: **the destructive script went to prod on a schema I never verified end-to-end**. Three separate design mistakes lined up to make it possible, and *not one* of them was caught before the script ran on a Tuesday night. Two signup paths feed the `users` table: | Population | ID source | |---|---| | Telegram users (most of base) | Positive integers — Telegram's own user IDs come in on the message envelope | | OAuth users (Google / Discord, web sign-in) | Negative integers from a Postgres sequence `web_user_id_seq` | OAuth IDs were negative on purpose — to keep them out of the positive Telegram-ID space and avoid collisions when a Telegram user later signed in via web. The minter in `api/web_auth.py` looked roughly like this: ``` php async def _allocate_negative_user_id(db) -> int: for _ in range(5): # retry on rare UniqueViolation new_id = -(await db.fetchval("SELECT nextval('web_user_id_seq')")) try: await db.execute("INSERT INTO users (id, ...) VALUES (%s, ...)", new_id) return new_id except UniqueViolation: # someone else took it; bump the sequence past current MIN(id) and retry await db.execute( "SELECT setval('web_user_id_seq', GREATEST(-MIN(id), currval('web_user_id_seq')))" " FROM users" ) raise RuntimeError("could not allocate user id after 5 retries") ``` The `setval(GREATEST(-MIN(id), current))` step is the load-bearing piece you have to keep in mind. It says: *whatever the most-negative users.id is right now, my sequence should be at least that far advanced, so I never collide with it again*. For QA I was creating test users by hand with **hardcoded negative IDs** like `-91111101` , `-91111102` , … via `INSERT ... ON CONFLICT (id) DO UPDATE` . Easy to remember, easy to clean up later by range. That choice triggered three independent failure modes, each on its own benign, lethal in combination: `web_user_id_seq` to 91 111 101.`setval(GREATEST(...))` line above, the very next OAuth signup retry saw the new test row with `id = -91111101` , computed `-MIN(id) = 91111101` , and advanced its own sequence. From that moment on, all real OAuth signups were drawing IDs in the neighbourhood of `-91111111` , `-91111112` , … — right inside the window where my test users lived.`INSERT ... ON CONFLICT (id) DO UPDATE` .`plan` , `auth_source` and several other fields instead of erroring.`DELETE … WHERE id BETWEEN -91111200 AND -91111100` None of these three behaviors is exotic. The `setval(GREATEST(...))` retry pattern is a normal way to handle UniqueViolation on a seeded sequence. `ON CONFLICT DO UPDATE` is a normal Postgres upsert. Range-DELETE is a normal cleanup pattern. **Each was safe on its own; the interaction of all three was lethal — and I never set up a staging run that would have surfaced the interaction before it touched prod.** A 30-second sanity check on the second insert ("did adding `id = -91111101` move `web_user_id_seq` ? what does the next OAuth signup land on?") would have shown the cascading effect immediately. Nobody — me — ran it. The cleanup script ran nightly for weeks looking healthy because real OAuth signup volume hadn't yet pushed a real ID into the deletion window. Recovery from Postgres backup was effectively impossible. The chain: `pg_dump` to Storj was about 22 hours old — `ON CONFLICT DO UPDATE` had already mutated their `plan` and `auth_source` columns earlier the same day.`users` pages were gone too.What we *could* salvage came from side channels: `PERSIST` -ed what looked important and reconstructed recent conversations for affected users.`chat_sessions` and `web_messages` Net: people kept their accounts and most of their *recent* conversations, but lost web-side scene context older than the Redis window. We comped the affected users. The cost of the incident wasn't the rows — it was the trust dent and the day-and-a-half of recovery work. `ON CONFLICT DO UPDATE` + range-DELETE) was never verified end-to-end before any of it touched production.`INSERT` of `id = -91111101` in staging followed by `SELECT id FROM users ORDER BY id LIMIT 5` , would have shown the sequence had jumped to the test neighbourhood. Nobody ran it. `BETWEEN` query can sweep. An attribute is something a `WHERE auth_source = 'test'` query cannot accidentally trip over.`INSERT ON CONFLICT (id) DO UPDATE` .`INSERT` would have failed loudly and surfaced the collision Any one of these five would have saved us; we had all five wrong. ``` ALTER TABLE users ADD COLUMN auth_source text NOT NULL DEFAULT 'oauth'; -- backfill: 'telegram' for positive Telegram IDs, 'oauth' for legacy negative, -- 'test' for known test rows that we then deleted via the new path. CREATE INDEX users_auth_source_idx ON users(auth_source); # scripts/test_user_factory.py TEST_ID_RANGE = (1_000_000_001, 1_999_999_999) # high *positive* — out of all real paths def create_test_user() -> int: user_id = _next_test_id() db.execute( "INSERT INTO users (id, auth_source, ...) VALUES (%s, 'test', ...)", (user_id, ...), ) return user_id php # scripts/test_user_cleanup.py def cleanup_test_users(dry_run: bool = True) -> int: rows = db.fetchall("SELECT id FROM users WHERE auth_source = 'test'") if dry_run: print(f"Would delete {len(rows)} test users") return len(rows) db.execute("DELETE FROM users WHERE auth_source = 'test'") return len(rows) ``` The script defaults to `dry_run=True` . The CLI flag to actually run it is explicit and shows the count first. We've also banned, in our engineering doc and in code review: any `DELETE … WHERE id BETWEEN …` on the `users` table, for any reason; any `INSERT … ON CONFLICT (id) DO UPDATE` on `users.id` . We rebuilt the backup story around explicit recovery point objectives. Off-site is **Storj** (~7 GB total, ~$0.03/month — cost is not the constraint). | Backup tier | Cadence | Destination | RPO | |---|---|---|---| Postgres `pg_dump` (logical) | Hourly | Local disk | ≤ 1 h | Postgres `pg_dump` (logical) | Daily | Storj S3 | ≤ 24 h | | Off-site cold copy | Weekly | Storj S3 | ≤ 7 d | | Redis snapshot (RDB) | Every 6 h | Local + Storj | ≤ 6 h | WAL archiving to S3-compatible storage is still pending — that's the next item. With it, RPO drops to seconds. Without it, hourly logical dumps are the floor. A backup you've never restored from is a hope, not a backup. We restore from yesterday's hourly dump into a scratch container monthly. The first time we tried, the restore script had bit-rotted and didn't compile. `DELETE` , in staging, against real data, and read the results" is thirty seconds of work. It is also the only thing that would have caught this.We've run the new contract for two weeks now. No range-DELETE incidents. The new `auth_source = 'test'` filter is boring and explicit and impossible to fat-finger. Boring is the goal. This postmortem is from production work at ** HoneyChat** — a Telegram-native AI companion. Canonical version: — *HoneyChat Engineering* `pg_dump` documentation