Context
Migration 003_admin_and_approval.sql:48 adds approval_status NOT NULL DEFAULT 'approved' to the users table. New rows inserted by the application layer override this with 'pending' (see src/store/users.ts:168).
Problem / Observation
The intent is documented inline ("set the default to 'approved' on backfill so existing accounts are not retroactively locked out, and switch the default for new rows to 'pending' at the application layer"). This is correct for an existing instance, but:
-
Operator who fresh-installs and then ALTERs the migration ordering (e.g. someone restoring from a partial backup) gets the schema default of 'approved' on every new row, bypassing the approval gate — because the route relies on the application-layer default. There is no DB-level CHECK preventing this.
-
No test asserts that after migration 003, existing users keep accessing the system AND that new registrations are 'pending'. The behavior is brittle.
-
The approval gate is the only line preventing arbitrary account creation on a public instance. A single dropped INSERT statement application-side silently regresses to open-registration. CHECK constraints would catch this immediately.
Proposed approach
- Change the application INSERT to always pass
approval_status explicitly (it already does — users.ts:170 runs with status as a positional bind) — but add a defensive CHECK in a new migration: CHECK (approval_status IN ('pending','approved','rejected')).
- Better: change the schema default to
'pending' in a follow-up migration, and document that operators upgrading from pre-003 need to manually UPDATE users SET approval_status='approved' for legacy accounts during the migration window. Document in docs/self-host.md "Upgrades".
- Add a test that runs 001 → 003 with seeded users and verifies the backfill.
Acceptance criteria
Context
Migration
003_admin_and_approval.sql:48addsapproval_status NOT NULL DEFAULT 'approved'to theuserstable. New rows inserted by the application layer override this with'pending'(see src/store/users.ts:168).Problem / Observation
The intent is documented inline ("set the default to 'approved' on backfill so existing accounts are not retroactively locked out, and switch the default for new rows to 'pending' at the application layer"). This is correct for an existing instance, but:
Operator who fresh-installs and then ALTERs the migration ordering (e.g. someone restoring from a partial backup) gets the schema default of
'approved'on every new row, bypassing the approval gate — because the route relies on the application-layer default. There is no DB-level CHECK preventing this.No test asserts that after migration 003, existing users keep accessing the system AND that new registrations are
'pending'. The behavior is brittle.The approval gate is the only line preventing arbitrary account creation on a public instance. A single dropped INSERT statement application-side silently regresses to open-registration. CHECK constraints would catch this immediately.
Proposed approach
approval_statusexplicitly (it already does —users.ts:170runs withstatusas a positional bind) — but add a defensiveCHECKin a new migration:CHECK (approval_status IN ('pending','approved','rejected')).'pending'in a follow-up migration, and document that operators upgrading from pre-003 need to manuallyUPDATE users SET approval_status='approved'for legacy accounts during the migration window. Document in docs/self-host.md "Upgrades".Acceptance criteria
users.approval_status