On 2026-05-11, a test-cleanup script on HoneyChat (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:
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:
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);
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
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