PrevEmployment: add FK user_account → employee.user_account with ON UPDATE CASCADE

Open
  • Is something wrong?
#141 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
55/100
Issue type
Feature
Clarity
Clearly specified
Activity status
Active
Tech stack
python, sqlalchemy
Domain
backend, databases

Research direction

Start with api/db/schema/prev_employment.py and the Alembic migration for #138 to follow the existing foreign key pattern. Check how migrations detect and report orphaned prev_employment rows, then add the constraint with ON UPDATE CASCADE and default delete behavior. Add tests for cascading employee account updates and rejecting unknown accounts; done when both behaviors pass and orphan handling is explicit.

Written by the indexing model from the issue text.

Description

PrevEmployment.user_account is a bare UUID column with no relation to Employee. Align it with the decision made for StudentDocuments in #138.

Goal

Add a foreign key PrevEmployment.user_account → employee.user_account with ON UPDATE CASCADE, so that:

  • no PrevEmployment row can reference a non-existent user (referential integrity)
  • remapping employee.user_account (e.g. during the planned account fusion migration) updates PrevEmployment automatically

Lookups keep using the JWT sub directly; no join through Employee is needed.

Tasks
  • Alembic migration adding the FK constraint (ON UPDATE CASCADE; delete behaviour stays as default/restrict)
  • Before adding the constraint: detect orphaned prev_employment rows whose user_account has no matching employee row, and decide how to handle them (delete / report). The migration must not fail silently.
  • Update the SQLModel schema api/db/schema/prev_employment.py (foreign_key="employee.user_account", ondelete/onupdate via sa_column_kwargs or sa_column)
  • Test: updating employee.user_account cascades to prev_employment.user_account
  • Test: inserting a PrevEmployment with an unknown user_account fails
Out of scope
  • Changes to the PrevEmployment endpoints

Related: #138

Dominant language
Python
Stars
0
Forks
0
Avg merge
39m
Merged PRs (30d)
10

Getting set up

  • Ships a Dockerfile or Docker Compose file
  • No pull request template
  • No contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from ClockGU/clockwork-backend

All issues in ClockGU/clockwork-backend

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.