Severity: Low
Labels: tech-debt, backend, P3
Location: backend/db/migration_cosmetics.sql:46-70; contrast the idempotent guard in migration_gradebook.sql:44-54
Description
These migrations are applied by hand ("Run once in SQL editor"). migration_cosmetics.sql issues five bare ALTER TABLE user_settings ADD CONSTRAINT fk_... with no guard; Postgres has no ADD CONSTRAINT IF NOT EXISTS, so a second run aborts with "constraint already exists", and (not wrapped in a transaction) leaves ambiguous partial state. The codebase already uses the safe DO $$ ... IF NOT EXISTS (SELECT 1 FROM pg_constraint ...) pattern in migration_gradebook.sql.
Suggested fix
Wrap each ADD CONSTRAINT in the DO $$ BEGIN IF NOT EXISTS (...) THEN ... END IF; END $$; guard.
Acceptance criteria
migration_cosmetics.sql is safely re-runnable (idempotent).
Severity: Low
Labels: tech-debt, backend, P3
Location:
backend/db/migration_cosmetics.sql:46-70; contrast the idempotent guard inmigration_gradebook.sql:44-54Description
These migrations are applied by hand ("Run once in SQL editor").
migration_cosmetics.sqlissues five bareALTER TABLE user_settings ADD CONSTRAINT fk_...with no guard; Postgres has noADD CONSTRAINT IF NOT EXISTS, so a second run aborts with "constraint already exists", and (not wrapped in a transaction) leaves ambiguous partial state. The codebase already uses the safeDO $$ ... IF NOT EXISTS (SELECT 1 FROM pg_constraint ...)pattern inmigration_gradebook.sql.Suggested fix
Wrap each
ADD CONSTRAINTin theDO $$ BEGIN IF NOT EXISTS (...) THEN ... END IF; END $$;guard.Acceptance criteria
migration_cosmetics.sqlis safely re-runnable (idempotent).