Database

SQLite schema, key tables, KanbanStage derivation, and Alembic migration workflow.

The backend uses SQLite for local development with a schema that is PostgreSQL-compatible. Switching databases requires only a change to the DB_ENGINE environment variable — no code changes.

Variable	Default	Description
`DB_ENGINE`	`sqlite`	Database engine (`sqlite` or `postgresql`)
`DATABASE_PATH`	`./database.db`	Path to the SQLite file (SQLite only)

Key tables

`user`

Column	Type	Notes
`id`	integer	Primary key
`email`	string	Unique
`password_hash`	string	Bcrypt hash — never returned to clients
`force_password_reset`	bool	When `true`, user must change password before using the portal
`fname`	string	First name
`lname`	string	Last name
`active`	bool	Soft-delete flag
`phone`	string	Optional
`bio`	string	Optional
`profile_image`	string	Path or URL to profile photo

`calling`

Column	Type	Notes
`id`	integer	Primary key
`name`	string	Unique
`max_slots`	integer	Maximum concurrent holders
`is_public`	bool	Whether the calling is visible on public-facing pages
`system_defined`	bool	`true` for High Councilor, Stake Presidency, Bishop — cannot be deleted via API

`usercalling`

Column	Type	Notes
`id`	integer	Primary key
`user_id`	integer	FK → `user`
`calling_id`	integer	FK → `calling`
`slot_number`	integer	Which slot this user occupies (1-indexed)

`permissions`

Column	Type	Notes
`id`	integer	Primary key
`foreign_id`	integer	User ID or Calling ID, depending on `is_calling`
`is_calling`	bool	`false` = user grant; `true` = calling grant (inherited by all holders)
`scopes`	integer	Bitmask of `Permission` IntFlag values

`ward`

Column	Type	Notes
`id`	integer	Primary key
`name`	string	Ward name
`bishop_id`	integer	FK → `usercalling` (the bishop's calling assignment row)
`start_time`	float	Meeting start time in decimal hours (e.g., `9.0` = 9:00 AM, `10.5` = 10:30 AM)

`callingproposal`

Column	Type	Notes
`id`	integer	Primary key
`fname` / `lname`	string	Person being called or released
`spouse_name`	string	Optional
`proposed_calling`	string	Free-text description of the calling
`ward_id`	integer	FK → `ward`
`submitter`	integer	FK → `user` (who submitted the proposal)
`is_release`	bool	`true` for releases, `false` for new callings
`submitted_at`	datetime

`kanbanupdate`

Column	Type	Notes
`id`	integer	Primary key
`proposal_id`	integer	FK → `callingproposal`
`updater_id`	integer	FK → `user`
`from_stage`	integer	Previous `KanbanStage` value
`to_stage`	integer	New `KanbanStage` value
`updated_at`	datetime

`callingcomment`

Column	Type	Notes
`id`	integer	Primary key
`proposal_id`	integer	FK → `callingproposal`
`commenter_id`	integer	FK → `user`
`comment_text`	string
`created_at`	datetime
`edited_at`	datetime	`null` if never edited

`callingapproval`

Column	Type	Notes
`id`	integer	Primary key
`proposal_id`	integer	FK → `callingproposal`
`approver_id`	integer	FK → `user`
`approved`	bool	`true` = approved, `false` = rejected
`created_at`	datetime

`usersession`

Column	Type	Notes
`id`	integer	Primary key
`user_id`	integer	FK → `user`
`token_hash`	string	SHA-256 hash of the refresh token
`ip_address`	string	Client IP at time of login
`expires_at`	datetime	After this time the session is invalid

KanbanStage derivation

KanbanStage is not a column on callingproposal. The current stage of a proposal is derived from the to_stage value of its most recent kanbanupdate row.

There is no single source-of-truth column — you must query the latest KanbanUpdate to determine where a proposal currently sits in the pipeline.

Value	Stage	Notes
0	`SP_APPROVAL`	Starting stage for new callings
1	`HC_APPROVAL`	Auto-advances after SP approval threshold is met
2	`INTERVIEW`	Starting stage for releases (skips 0–1)
3	`SUSTAIN`	After interview
4	`SET_APART`	After sustaining (callings only; releases skip this stage)
5	`LCR_UPDATE`	After set apart
6	`DONE`	Terminal stage

Auto-advance thresholds are controlled by the SP_APPROVAL_THRESHOLD and HC_APPROVAL_THRESHOLD environment variables.

Alembic migrations

Always run Alembic commands from the backend/ directory.

uv run alembic -c alembic.ini revision --autogenerate -m "add my column"

uv run alembic -c alembic.ini upgrade head

uv run alembic -c alembic.ini downgrade -1

uv run alembic -c alembic.ini history --verbose

Autogenerate compares the current SQLModel models against the current database schema. Make sure your model changes are saved before running revision --autogenerate.

Tests override the get_session() FastAPI dependency with a session bound to a temporary SQLite file created at test start and deleted at test end. The real database.db is never touched during a test run.

This means migrations do not need to be applied before running tests — the test database is created fresh from SQLModel metadata each time.

# Run all tests
uv run pytest

# Run a single test file
uv run pytest src/tests/test_users.py

# Run a single test
uv run pytest src/tests/test_auth.py::test_login_sets_cookie_and_returns_token