Database layer¶

SQLModel-based database layer for BeCoMe API.

Tables¶

Table	Description
`users`	User accounts with authentication data
`projects`	Decision-making projects with scale configuration
`project_members`	Many-to-many: users ↔ projects with roles
`invitations`	Per-user invitations to join a project (unique per project+invitee)
`expert_opinions`	Fuzzy triangular numbers from experts (unique per user+project)
`calculation_results`	Cached BeCoMe calculation results. Holds the numbers only: the Likert agreement verdict is derived from the project scale on read (`api/services/likert_verdict.py`), never stored, so it cannot drift from the project
`password_reset_tokens`	Tokens for password reset via email
`email_verification_tokens`	Activation tokens for the email verification flow, stored as SHA-256 hashes

Entity relationships¶

users (1:N) ──► projects (admin ownership)
      │
      ├─(N:M)─► project_members ◄─(N:M)─ projects
      │
      ├─(1:N)─► expert_opinions ◄─(N:1)─ projects
      │
      ├─(1:N)─► password_reset_tokens
      │
      ├─(1:N)─► email_verification_tokens
      │
      └─(1:N)─► invitations (invitee_id, inviter_id) ◄─(N:1)─ projects

projects (1:1) ──► calculation_results

Usage¶

Creating tables¶

from api.db.engine import create_db_and_tables

create_db_and_tables()

On SQLite (local development and the test suite), the FastAPI lifespan hook calls create_db_and_tables() at startup. Alembic owns the deployed PostgreSQL schemas instead: the migrations live in migrations/ and run before each Railway deploy, and create_db_and_tables() returns without doing anything there. For how schema management works, see Environments.

Session dependency¶

from fastapi import Depends
from sqlmodel import Session

from api.db.session import get_session


@app.get("/users/{user_id}")
def get_user(user_id: UUID, session: Session = Depends(get_session)):
    user = session.get(User, user_id)
    return user

Model examples¶

from api.db.models import User, Project, ExpertOpinion, MemberRole

# Create user
user = User(
    email="[email protected]",
    hashed_password="$2b$12$...",
    first_name="John",
    last_name="Doe",
)

# Create project
project = Project(
    name="Budget Allocation 2024",
    description="Annual budget distribution",
    admin_id=user.id,
    scale_min=0.0,
    scale_max=100.0,
    scale_unit="%",
)

# Add expert opinion (fuzzy triangular number)
opinion = ExpertOpinion(
    project_id=project.id,
    user_id=user.id,
    position="Financial Analyst",
    lower_bound=30.0,  # pessimistic
    peak=45.0,  # most likely
    upper_bound=60.0,  # optimistic
)

Configuration¶

Set the database URL with the DATABASE_URL environment variable:

# Local development: the PostgreSQL from docker/docker-compose.yml
#   docker compose -f docker/docker-compose.yml up -d db
DATABASE_URL=postgresql://become:become@localhost:5432/become

# Deployed environments: the least-privilege become_app role on the managed instance
DATABASE_URL=postgresql://user:pass@host:5432/become

# Fallback when Docker is not available. Boots, but uses create_all rather than
# Alembic, so it never exercises a migration.
DATABASE_URL=sqlite:///./become.db

File structure¶

api/db/
├── __init__.py     # Package marker
├── engine.py       # Database engine and table creation
├── models.py       # SQLModel table definitions
├── session.py      # Database session management (get_session)
├── utils.py        # Utilities (utc_now, ensure_utc, EMAIL_REGEX)
└── README.md       # This file