src/db/users.ts — Users repository
The only module that reads or writes the users table. Two functions today;
more (approveUser, setRole, blockUser) land in Phase 3.
Public surface
| Export | Shape | Purpose |
|---|---|---|
User | {telegramUserId, username, firstName, status, role, createdAt, approvedAt, approvedBy} | Domain shape |
UserStatus | 'pending' | 'active' | 'blocked' | |
UserRole | 'admin' | 'user' | |
NewUserInput | {telegramUserId, username, firstName} | Insert payload |
findOrCreatePendingUser(db, input) | (Queryable, NewUserInput) => Promise<User> | Insert-if-missing |
bootstrapAdmin(db, telegramUserId) | (Queryable, number) => Promise<User> | Boot-time seed |
Behaviors
findOrCreatePendingUser
ON CONFLICT (telegram_user_id) DO UPDATE SET username/first_name = COALESCE(EXCLUDED.x, users.x). Idempotent for repeat messages; the
COALESCE means an inbound update with a freshly-set username overrides a
previously-null one, but never erases a value Telegram briefly omits.
bootstrapAdmin
ON CONFLICT (telegram_user_id) DO UPDATE SET status='active', role='admin', approved_at = COALESCE(users.approved_at, now()). Solves the chicken-and-egg
of the first admin — they can’t be approved by another admin. Safe to call
on every boot.
If the row already exists (admin previously messaged the bot as a pending
user before being added to env), it’s promoted in place. The
COALESCE(users.approved_at, now()) clause means re-running on an already-
active admin doesn’t bump approved_at, so the audit trail is stable.
Row mapping
The DB returns telegram_user_id and approved_by as strings (pg’s default
for bigint columns, to avoid precision loss). We map both to number at
the boundary because Telegram IDs fit safely inside Number.MAX_SAFE_INTEGER
for the foreseeable future. If that ever changes, this is the single place
to switch to bigint.
Schema
Defined in src/db/migrations/1747200000000_create-users.js and documented in docs/plan/data-and-contracts.md.
Tests
test/unit/users.repo.test.ts — six
integration-flavored tests against the docker-compose Postgres. Each runs
inside a withRollback transaction so the table is empty for the next test.
Skipped automatically when the DB isn’t reachable (see
test/helpers/db.ts).