Add an Appointments Model in FastAPI

Extend an async FastAPI healthcare platform with a new SQLAlchemy model and migration.

Introduction

30 Second Summary

Every healthcare platform eventually needs to track when patients meet their doctors. The scheduling layer is where data modeling patterns like enums, foreign keys, and timestamps all come together in one model.

In this project, you will define an Appointment model using SQLAlchemy 2.0's modern Mapped syntax, generate an Alembic migration, and verify the resulting table in a Dockerized PostgreSQL database.

What You'll Build

An Appointment model integrated into an existing async FastAPI healthcare platform, complete with a PostgreSQL enum type, UUID foreign keys, and a verified Alembic migration.

By the end of this project, you'll have:

  • An AppointmentStatus enum class that stores lowercase string values directly in PostgreSQL.
  • A fully typed Appointment model with UUID foreign keys linking to existing intake_cases and users tables.
  • A verified Alembic migration that creates both the enum type and the appointments table in your Dockerized PostgreSQL.
  • Secret Mission: Add a unique constraint to prevent double-booking the same doctor at the same time slot.

Are there any prerequisites?

This project assumes you have an existing FastAPI codebase with async SQLAlchemy models (Base, UUIDPrimaryKeyMixin, TimestampMixin), Alembic configured with the async template, and PostgreSQL running via Docker Compose.

You should also have VS Code, Python 3.13, and Docker Desktop installed on your machine.

Define the AppointmentStatus Enum

Every appointment in a healthcare platform needs a status. Whether it's newly suggested, confirmed by the patient, or cancelled, that status drives the entire scheduling workflow.

In this step, you'll create a new model file and define an AppointmentStatus enum. You'll follow the exact pattern already used by ConsentStatus in your existing codebase to keep things consistent.

In this step, get ready to:
  • Create the app/models/appointment.py file.
  • Define the AppointmentStatus enum with three members.
  • Verify the enum works correctly in a Python REPL.
Create the appointment model file

Your existing project already has a app/models/ directory with base.py in it. You'll add a new file alongside it for the appointment model.

  • In the Explorer sidebar in VS Code, right-click the app/models/ folder.
  • Select New File.
  • Name it appointment.py and press Enter.
  • Paste the following code into appointment.py:
import enum
import uuid
from datetime import datetime

from sqlalchemy import Enum, ForeignKey, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column

from app.models.base import Base, UUIDPrimaryKeyMixin, TimestampMixin


class AppointmentStatus(enum.StrEnum):
    SUGGESTED = "suggested"
    CONFIRMED = "confirmed"
    CANCELLED = "cancelled"

What does this code do?

The imports at the top pull in everything you'll need for both this step and the next step (when you define the full model). The key piece right now is import enum, which gives you access to StrEnum.

The AppointmentStatus class defines three possible states for an appointment: SUGGESTED, CONFIRMED, and CANCELLED. Each member maps to a lowercase string value that will be stored in PostgreSQL.

Why StrEnum instead of regular Enum?

With enum.StrEnum, each member IS its string value. You can compare AppointmentStatus.SUGGESTED == "suggested" directly without needing .value. This simplifies serialization in API responses and database queries.

Later, when you wire this up to a PostgreSQL column, the values_callable=lambda x: [e.value for e in x] pattern ensures PostgreSQL stores the lowercase string values ("suggested"), not the Python member names ("SUGGESTED"). This matches the existing ConsentStatus pattern in your codebase.

  • Save appointment.py.

Seeing a red underline on the imports?

If VS Code shows import errors for sqlalchemy or app.models.base, make sure your Python interpreter in VS Code is set to the project's virtual environment. Press Cmd+Shift+P (macOS) or Ctrl+Shift+P (Windows) and select Python: Select Interpreter, then choose the one inside your project's .venv folder.

Help me fix import errors in my appointment.py file

Verify the enum in a Python REPL

Before moving on, confirm that your enum is importable and its values serialize correctly.

  • In your terminal (with your virtual environment active), verify the enum by running this command:
python -c "from app.models.appointment import AppointmentStatus; print(AppointmentStatus.SUGGESTED.value == 'suggested')"

You should see True printed in the terminal. This confirms the enum is importable and its values match the expected lowercase strings.

Seeing ModuleNotFoundError or ImportError?

Make sure you're running the command from your project's root directory (the folder that contains the app/ folder). Also confirm your virtual environment is active. You should see (.venv) or similar at the start of your terminal prompt.

If you see a NameError about StrEnum, confirm you're using Python 3.11 or later. StrEnum was added to the standard library in Python 3.11.

Help me debug my AppointmentStatus import error

✔️ Awesome, I've got everything!

Great. Double check you've saved app/models/appointment.py before moving on.

ⓧ I'd like to double check the full code

Here's the complete app/models/appointment.py file so far:

import enum
import uuid
from datetime import datetime

from sqlalchemy import Enum, ForeignKey, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column

from app.models.base import Base, UUIDPrimaryKeyMixin, TimestampMixin


class AppointmentStatus(enum.StrEnum):
    SUGGESTED = "suggested"
    CONFIRMED = "confirmed"
    CANCELLED = "cancelled"

Your enum is defined and verified. Next up, you'll build the full Appointment model with foreign keys, a datetime slot, and this enum wired into a PostgreSQL column.

Define the Appointment Model

In the previous step, you defined the AppointmentStatus enum that describes the lifecycle of an appointment. Now you need the actual model that uses it.

The Appointment model ties together a patient, a doctor, a time slot, and a status. It connects to the existing intake_cases and users tables through foreign keys, following the same mixin conventions your codebase already uses.

In this step, get ready to:
  • Define the Appointment model class with UUID foreign keys, a datetime slot, and an enum status column.
  • Verify the model imports without errors.
Add the Appointment class to your model file

The Appointment class inherits from the same base and mixins the rest of your codebase uses. This gives it a UUID primary key and automatic created_at/updated_at timestamps for free.

  • In app/models/appointment.py, find the closing line of your AppointmentStatus enum class, then add the following code below it:
class Appointment(Base, UUIDPrimaryKeyMixin, TimestampMixin):
    __tablename__ = "appointments"

    # Foreign key linking to the patient's intake case
    patient_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("intake_cases.id"),
        nullable=False,
    )
    # Foreign key linking to the doctor (a user with a doctor role)
    doctor_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("users.id"),
        nullable=False,
    )
    # The scheduled date and time for the appointment
    time_slot: Mapped[datetime] = mapped_column(nullable=False)
    # Appointment lifecycle status, stored as a PostgreSQL enum type
    status: Mapped[AppointmentStatus] = mapped_column(
        Enum(
            AppointmentStatus,
            name="appointment_status",
            values_callable=lambda x: [e.value for e in x],
        ),
        nullable=False,
        default=AppointmentStatus.SUGGESTED,
    )

What does this code do?

This class defines the appointments table in PostgreSQL. Here are the key design decisions:

  • patient_id and doctor_id use Mapped[uuid.UUID] which automatically resolves to PostgreSQL's native UUID type. This matches the UUID primary keys on the referenced tables.
  • nullable=False on every column enforces that an appointment must always have a patient, doctor, time, and status. An appointment without any of these is meaningless.
  • default=AppointmentStatus.SUGGESTED sets the workflow starting point. New appointments begin as suggestions before being confirmed or cancelled.
  • values_callable=lambda x: [e.value for e in x] ensures PostgreSQL stores the lowercase string values ("suggested") rather than the Python member names ("SUGGESTED").
  • Save app/models/appointment.py.
Verify the model imports correctly

A quick import check catches typos, missing imports, or indentation errors before you try to generate a migration.

  • Confirm the model loads without errors by running this command in your terminal:
python -c "from app.models.appointment import Appointment"

If the command returns with no output, your model is valid. Any ImportError or NameError means something is missing or misspelled in your file.

Seeing an ImportError or NameError?

  • Check that your class definition starts with class Appointment(Base, UUIDPrimaryKeyMixin, TimestampMixin): exactly. A typo in any of the parent classes triggers a NameError.
  • Confirm the import line from app.models.base import Base, UUIDPrimaryKeyMixin, TimestampMixin is still at the top of your file. If it was accidentally deleted, the class has no access to its parent classes.
  • Make sure you're running the command from the project root directory (the folder that contains the app/ folder). Running from inside app/models/ breaks the import path.

Help me debug my Appointment model import error

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

Here is the complete app/models/appointment.py file at this point:

import enum
import uuid
from datetime import datetime

from sqlalchemy import Enum, ForeignKey, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column

from app.models.base import Base, UUIDPrimaryKeyMixin, TimestampMixin


class AppointmentStatus(enum.StrEnum):
    SUGGESTED = "suggested"
    CONFIRMED = "confirmed"
    CANCELLED = "cancelled"


class Appointment(Base, UUIDPrimaryKeyMixin, TimestampMixin):
    __tablename__ = "appointments"

    patient_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("intake_cases.id"),
        nullable=False,
    )
    doctor_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("users.id"),
        nullable=False,
    )
    time_slot: Mapped[datetime] = mapped_column(nullable=False)
    status: Mapped[AppointmentStatus] = mapped_column(
        Enum(
            AppointmentStatus,
            name="appointment_status",
            values_callable=lambda x: [e.value for e in x],
        ),
        nullable=False,
        default=AppointmentStatus.SUGGESTED,
    )

Why Mapped[uuid.UUID] for foreign keys?

The existing tables (intake_cases and users) use UUIDPrimaryKeyMixin for their primary keys. Foreign key columns must match the type of the column they reference.

In SQLAlchemy 2.0, Mapped[uuid.UUID] automatically maps to PostgreSQL's native UUID type. No explicit type declaration needed.

Your model is defined and Python-valid. Next, you'll register it with Alembic and autogenerate the migration that will create the actual table in PostgreSQL.

Register the Model and Autogenerate the Migration

Your Appointment model is defined, but Alembic has no idea it exists.

Alembic discovers new tables by inspecting Base.metadata. If your model file is never imported into the chain that builds that metadata, the autogenerate command compares the database against an empty picture and produces nothing. In this step, you will register the model and let Alembic generate the migration script for you.

In this step, get ready to:
  • Register the Appointment model so Alembic can detect it.
  • Autogenerate a migration that creates both the appointment_status enum type and the appointments table.
  • Confirm the migration file contains the expected operations.
Register the Appointment model import

Alembic's --autogenerate flag works by comparing Base.metadata (all models that have been imported) against the live database schema. Without importing the new model somewhere in the chain that feeds target_metadata in env.py, the migration will come out empty.

  • Open app/models/__init__.py in VS Code. This is the file that aggregates all model imports for the project.
  • Add the following import line below the existing imports:
from app.models.appointment import Appointment

Why does this single import matter?

When Python executes this import, the Appointment class registers itself with Base.metadata. That metadata object is what Alembic reads through target_metadata in env.py.

Without this line, Alembic compares the database against an empty set of models and generates a migration with no operations.

  • Save app/models/__init__.py.

✔️ Awesome, I've got everything!

Great. Make sure the file is saved before running the autogenerate command next.

ⓧ I'd like to double check the full code

# existing imports ...
from app.models.appointment import Appointment
Run the autogenerate command

Alembic's autogenerate compares the models registered in Base.metadata against the current database schema. It then writes a migration script containing the operations needed to bring the database in sync.

  • Generate the migration by running this command in your terminal:
alembic revision --autogenerate -m "add appointments table"

What does this command do?

  • alembic revision creates a new migration file in the alembic/versions/ directory.
  • --autogenerate tells Alembic to compare Base.metadata against the live PostgreSQL database and populate the upgrade/downgrade functions automatically.
  • -m "add appointments table" adds a human-readable message that becomes part of the filename.
  • Confirm a new file appeared in alembic/versions/ by running:
ls alembic/versions/

You should see a new Python file with a revision hash and add_appointments_table in its name.

Getting an empty migration?

  • Make sure you saved app/models/__init__.py with the new import line. Without it, Alembic sees no new models.
  • Verify your env.py has target_metadata = Base.metadata and that the import chain reaches app/models/__init__.py.
  • Confirm that your Docker PostgreSQL container is running. Alembic needs a live database connection to compare against.

Help me debug my empty migration

Verify the migration file contents

The autogenerated file needs a quick review before you run it. Open the new migration file in alembic/versions/ and confirm two things.

  • Open the newly generated migration file in VS Code (click it in the Explorer sidebar under alembic/versions/).
  • Look for a CREATE TYPE appointment_status operation in the upgrade() function. This creates the PostgreSQL enum type with three values.
  • Look for a CREATE TABLE appointments operation. Confirm it includes columns for id, patient_id, doctor_id, time_slot, status, created_at, and updated_at.
  • Check that the downgrade() function drops the table first, then drops the appointment_status type. PostgreSQL enum types are independent objects that persist even after the table is deleted.

Why check the downgrade function?

In PostgreSQL, CREATE TYPE creates a standalone database object. Dropping the appointments table alone will NOT remove the appointment_status enum type.

The downgrade must explicitly DROP TYPE appointment_status after dropping the table, or future migrations could conflict with a leftover type.

Your migration is ready to run. Next up, you will review the details one more time and apply it against your Dockerized PostgreSQL database.

Review and Run the Migration

Your Alembic migration file is sitting in alembic/versions/, ready to reshape your database. It contains the instructions to create both the appointment_status enum type and the appointments table in PostgreSQL.

Autogenerated migrations are convenient, but they can contain subtle issues. In this step, you'll review the migration for correctness, apply it, and verify the results directly in the database.

In this step, get ready to:
  • Review the migration file for correct enum values, foreign keys, and downgrade logic.
  • Apply the migration to your PostgreSQL database.
  • Verify the table and enum type exist with the correct structure.
Review the migration file for correctness

Before applying anything to your database, it's worth reading through the autogenerated migration to catch issues early.

  • Open the new migration file in the alembic/versions/ directory.
  • Check that the enum definition uses the lowercase string values, not the Python member names:
# You should see something like this in the upgrade function:
sa.Enum('suggested', 'confirmed', 'cancelled', name='appointment_status')
  • Confirm the foreign keys reference intake_cases.id and users.id (matching the existing tables).
  • Scroll to the downgrade function and verify it includes DROP TYPE appointment_status after dropping the table.

Why check the downgrade function?

PostgreSQL enum types are independent database objects. Dropping the appointments table does NOT automatically drop the appointment_status enum type.

If the downgrade forgets to drop the type, rolling back the migration leaves an orphaned enum in your database. The next upgrade would then fail because the type already exists.

Seeing uppercase values like SUGGESTED?

If your enum definition shows 'SUGGESTED' instead of 'suggested', the values_callable in your model might be missing or incorrect. Go back to app/models/appointment.py and confirm you have values_callable=lambda x: [e.value for e in x] in your Enum() declaration. Then delete the migration file and regenerate it.

Why is my migration showing uppercase enum values?

Apply the migration

With the migration reviewed and looking correct, it's time to apply it. The existing env.py runs migrations through the async engine using asyncpg.

  • Apply the migration by running:
alembic upgrade head

What does this command do?

The alembic upgrade head command applies all pending migrations up to the latest revision. It connects through your async engine, creates the appointment_status enum type first, then creates the appointments table with all columns, constraints, and foreign keys.

  • You should see output indicating the migration was applied successfully, with the revision hash and message add appointments table shown.

Getting a connection error?

Make sure your PostgreSQL container is running. Check with docker compose ps and confirm the database service shows a healthy status.

Also verify your database connection string in alembic.ini or your environment variables matches the credentials used by the Docker container.

Why is my Alembic migration failing to connect to PostgreSQL?

Verify the table and enum type in PostgreSQL

The migration reported success, but let's confirm the results directly in the database. You'll connect to your PostgreSQL container and inspect the new objects.

  • Connect to your PostgreSQL database via psql in the running Docker container.
  • List the appointments table by running:
\dt appointments
  • You should see a row listing the appointments table in the public schema with type table.
  • Inspect the enum type and its values by running:
\dT+ appointment_status
  • You should see the appointment_status type with three elements: suggested, confirmed, and cancelled.

What do these psql commands show?

The \dt command lists tables matching the given pattern. It confirms the appointments table was created in the database.

The \dT+ command lists data types with extended details. For enum types, it shows the allowed values. This confirms PostgreSQL stored the lowercase string values (suggested, not SUGGESTED).

Table not showing up?

Make sure you're connected to the correct database. If your project uses a database name other than the default, specify it when connecting with psql.

If \dt appointments returns nothing, run alembic current to check whether the migration was actually applied. If it shows no current revision, the upgrade may have silently failed.

Why can't I see my appointments table in PostgreSQL after running the migration?

Secret mission

Prevent Double-Booking with a Unique Constraint

Your appointments table works, but nothing stops a doctor from being double-booked at the same time slot. In this secret mission, you'll enforce that business rule at the database level using a composite unique constraint.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally via Docker Compose, so there are no ongoing cloud costs.

Resources you used:

  • PostgreSQL appointments table and appointment_status enum type (inside the Docker container).
  • Two Alembic migration files in alembic/versions/.
  • New model file app/models/appointment.py.
  • Modified import in app/models/__init__.py.

Keep everything running

No action needed. Choose this if you're still actively building on the healthcare platform. Your new Appointment model, migrations, and database table stay in place, ready for you to wire up API routes or extend with additional columns.

Pause - I'll come back to this later

Stop the Docker containers to free up memory. Your data and migration files persist on disk.

  • In Docker Desktop, stop the PostgreSQL container (or stop the full Compose stack if nothing else depends on it).
  • When you're ready to resume, start the container again. Your appointments table and enum type will still be there.

Delete - I don't want to use this again

Remove all project resources to return the codebase to its pre-project state.

  • Roll back the unique-constraint migration by running:
  • Roll back the appointments table migration by running:
alembic downgrade -1
alembic downgrade -1

The first command drops the uq_doctor_time_slot constraint. The second drops the appointments table and the appointment_status enum type.

  • Delete the two migration files from alembic/versions/ (the files ending in _add_appointments_table.py and _add_doctor_time_slot_unique_constraint.py).
  • Delete the model file:
rm app/models/appointment.py
  • Open app/models/__init__.py and remove the line:
from app.models.appointment import Appointment
  • Save the file. Your codebase is now back to its original state.

Nice Work!

Nice Work!

Nice work! You just extended an async FastAPI healthcare platform with a fully integrated Appointment model, complete with enum-based status tracking and verified database migrations.

You've learned how to:

  • Defined an AppointmentStatus enum using StrEnum with an explicit values_callable to store lowercase string values in PostgreSQL.
  • Built an Appointment model with UUID foreign keys, datetime handling, and explicit PostgreSQL enum column declaration using SQLAlchemy 2.0 Mapped and mapped_column.
  • Registered the model, autogenerated an Alembic migration using the async pattern, and applied it against Dockerized PostgreSQL to create both the enum type and table.
  • Secret Mission: Added a composite unique constraint on doctor_id and time_slot to enforce no double-booking at the database level.

Ready to quiz yourself?