The Data Model

A database schema is the most permanent decision in a SaaS application. Endpoints can be rewritten. Frontend components can be replaced. A database migration that renames a column requires updating every query, every ORM model, and every API schema that references it, deploying them in the correct order, and praying that no query runs against the old schema during the transition. Design the schema carefully. Change it reluctantly.

Marketflow’s data model has six core tables: markets, stalls, vendors, applications, bookings, and market_days. Every table except vendors is scoped to a market, and every market belongs to an organizer. This scoping is the foundation of multi-tenant isolation. An organizer can query, update, and delete data in their markets. They cannot see, reference, or accidentally modify another organizer’s data.

The Feature

A market organizer creates a market, defines stalls, receives vendor applications, accepts or rejects them, assigns vendors to stalls, and creates market days with bookings. The database schema supports all of these operations with proper referential integrity, and Alembic migrations create the schema reproducibly.

The Decision

PostgreSQL over MySQL or SQLite. PostgreSQL provides UUID generation (gen_random_uuid()), native JSON columns for flexible metadata, row-level security policies (used in Chapter 6), and array types for multi-value fields. MySQL lacks RLS entirely. SQLite lacks concurrent write support, which matters the moment two organizers use the application simultaneously.

SQLAlchemy 2.0 over raw SQL or another ORM. SQLAlchemy 2.0’s Mapped type hints provide compile-time checking of column types. The async session integrates with FastAPI’s async endpoints. The alternative, raw asyncpg queries, is faster but loses schema validation, migration generation, and the ability to express relationships declaratively.

Alembic for migrations, always. Every schema change goes through Alembic. alembic revision --autogenerate diffs the SQLAlchemy models against the database and generates a migration. Manual schema changes (running ALTER TABLE in a psql session) are forbidden because they create drift between the migration history and the actual schema.

The Implementation

Base Model

# backend/app/models/base.py
import uuid as uuid_mod
from datetime import datetime

from sqlalchemy import text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(
        server_default=text("now()"),
    )
    updated_at: Mapped[datetime] = mapped_column(
        server_default=text("now()"),
        onupdate=datetime.utcnow,
    )


class UUIDMixin:
    id: Mapped[uuid_mod.UUID] = mapped_column(
        primary_key=True,
        server_default=text("gen_random_uuid()"),
    )

Market Model

# backend/app/models/market.py
import uuid as uuid_mod

from sqlalchemy import String, Text
from sqlalchemy.orm import Mapped, mapped_column, relationship

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


class Market(UUIDMixin, TimestampMixin, Base):
    __tablename__ = "markets"

    organizer_id: Mapped[uuid_mod.UUID] = mapped_column(index=True)
    name: Mapped[str] = mapped_column(String(200))
    description: Mapped[str | None] = mapped_column(Text)
    location: Mapped[str] = mapped_column(String(500))
    is_active: Mapped[bool] = mapped_column(default=True)

    stalls: Mapped[list["Stall"]] = relationship(
        back_populates="market", cascade="all, delete-orphan"
    )
    applications: Mapped[list["Application"]] = relationship(
        back_populates="market", cascade="all, delete-orphan"
    )
    market_days: Mapped[list["MarketDay"]] = relationship(
        back_populates="market", cascade="all, delete-orphan"
    )

Stall Model

# backend/app/models/stall.py
import uuid as uuid_mod

from sqlalchemy import ForeignKey, String
from sqlalchemy.orm import Mapped, mapped_column, relationship

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


class Stall(UUIDMixin, TimestampMixin, Base):
    __tablename__ = "stalls"

    market_id: Mapped[uuid_mod.UUID] = mapped_column(
        ForeignKey("markets.id", ondelete="CASCADE"),
        index=True,
    )
    label: Mapped[str] = mapped_column(String(50))
    size_category: Mapped[str] = mapped_column(String(20))
    price_per_day_cents: Mapped[int | None] = mapped_column()

    market: Mapped["Market"] = relationship(back_populates="stalls")
    bookings: Mapped[list["Booking"]] = relationship(back_populates="stall")

Vendor Model

# backend/app/models/vendor.py
import uuid as uuid_mod

from sqlalchemy import String, Text
from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy.orm import Mapped, mapped_column, relationship

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


class Vendor(UUIDMixin, TimestampMixin, Base):
    __tablename__ = "vendors"

    user_id: Mapped[uuid_mod.UUID] = mapped_column(unique=True, index=True)
    business_name: Mapped[str] = mapped_column(String(200))
    email: Mapped[str] = mapped_column(String(254))
    product_categories: Mapped[list[str]] = mapped_column(ARRAY(String(50)))
    description: Mapped[str | None] = mapped_column(Text)
    phone: Mapped[str | None] = mapped_column(String(20))

    applications: Mapped[list["Application"]] = relationship(
        back_populates="vendor"
    )

Application Model

# backend/app/models/application.py
import enum
import uuid as uuid_mod

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

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


class ApplicationStatus(str, enum.Enum):
    PENDING = "pending"
    ACCEPTED = "accepted"
    REJECTED = "rejected"
    WAITLISTED = "waitlisted"


class Application(UUIDMixin, TimestampMixin, Base):
    __tablename__ = "applications"
    __table_args__ = (
        UniqueConstraint("vendor_id", "market_id", name="uq_vendor_market"),
    )

    vendor_id: Mapped[uuid_mod.UUID] = mapped_column(
        ForeignKey("vendors.id", ondelete="CASCADE"),
        index=True,
    )
    market_id: Mapped[uuid_mod.UUID] = mapped_column(
        ForeignKey("markets.id", ondelete="CASCADE"),
        index=True,
    )
    status: Mapped[ApplicationStatus] = mapped_column(
        Enum(ApplicationStatus),
        default=ApplicationStatus.PENDING,
    )
    message: Mapped[str | None] = mapped_column(Text)

    vendor: Mapped["Vendor"] = relationship(back_populates="applications")
    market: Mapped["Market"] = relationship(back_populates="applications")

The UniqueConstraint on (vendor_id, market_id) prevents a vendor from submitting multiple applications to the same market. Without it, the application list fills with duplicates and the organizer cannot distinguish between the vendor’s first application and their fifth retry.

Booking and MarketDay Models

# backend/app/models/market_day.py
import uuid as uuid_mod
from datetime import date, time

from sqlalchemy import Date, ForeignKey, Time
from sqlalchemy.orm import Mapped, mapped_column, relationship

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


class MarketDay(UUIDMixin, TimestampMixin, Base):
    __tablename__ = "market_days"

    market_id: Mapped[uuid_mod.UUID] = mapped_column(
        ForeignKey("markets.id", ondelete="CASCADE"),
        index=True,
    )
    date: Mapped[date] = mapped_column(Date)
    start_time: Mapped[time] = mapped_column(Time)
    end_time: Mapped[time] = mapped_column(Time)
    is_cancelled: Mapped[bool] = mapped_column(default=False)

    market: Mapped["Market"] = relationship(back_populates="market_days")
    bookings: Mapped[list["Booking"]] = relationship(
        back_populates="market_day", cascade="all, delete-orphan"
    )

# backend/app/models/booking.py
import enum
import uuid as uuid_mod

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

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


class PaymentStatus(str, enum.Enum):
    PENDING = "pending"
    PAID = "paid"
    REFUNDED = "refunded"
    WAIVED = "waived"


class Booking(UUIDMixin, TimestampMixin, Base):
    __tablename__ = "bookings"
    __table_args__ = (
        UniqueConstraint(
            "stall_id", "market_day_id", name="uq_stall_market_day"
        ),
    )

    stall_id: Mapped[uuid_mod.UUID] = mapped_column(
        ForeignKey("stalls.id", ondelete="CASCADE"),
        index=True,
    )
    vendor_id: Mapped[uuid_mod.UUID] = mapped_column(
        ForeignKey("vendors.id", ondelete="CASCADE"),
        index=True,
    )
    market_day_id: Mapped[uuid_mod.UUID] = mapped_column(
        ForeignKey("market_days.id", ondelete="CASCADE"),
        index=True,
    )
    fee_cents: Mapped[int] = mapped_column(default=0)
    payment_status: Mapped[PaymentStatus] = mapped_column(
        Enum(PaymentStatus),
        default=PaymentStatus.PENDING,
    )

    stall: Mapped["Stall"] = relationship(back_populates="bookings")
    vendor: Mapped["Vendor"] = relationship()
    market_day: Mapped["MarketDay"] = relationship(back_populates="bookings")

The UniqueConstraint on (stall_id, market_day_id) prevents double-booking. One stall, one market day, one vendor. The database enforces this regardless of what the application code does. Constraints at the database level are not optional when data integrity matters.

Alembic Setup

# Inside the backend container
cd /app
uv run alembic init alembic

# backend/alembic/env.py
import asyncio
from logging.config import fileConfig

from alembic import context
from sqlalchemy.ext.asyncio import create_async_engine

from app.config import settings
from app.models.base import Base

# Import all models so Alembic sees them
from app.models import market, stall, vendor, application, booking, market_day  # noqa

config = context.config
fileConfig(config.config_file_name)

target_metadata = Base.metadata


def run_migrations_offline() -> None:
    context.configure(
        url=settings.database_url,
        target_metadata=target_metadata,
        literal_binds=True,
    )
    with context.begin_transaction():
        context.run_migrations()


def do_run_migrations(connection):
    context.configure(connection=connection, target_metadata=target_metadata)
    with context.begin_transaction():
        context.run_migrations()


async def run_migrations_online() -> None:
    connectable = create_async_engine(settings.database_url)
    async with connectable.connect() as connection:
        await connection.run_sync(do_run_migrations)
    await connectable.dispose()


if context.is_offline_mode():
    run_migrations_offline()
else:
    asyncio.run(run_migrations_online())

# Generate the initial migration
docker compose exec backend uv run alembic revision --autogenerate -m "initial schema"

# Apply the migration
docker compose exec backend uv run alembic upgrade head

Every model change follows the same pattern: modify the SQLAlchemy model, generate a migration with --autogenerate, review the generated migration file (autogenerate is not perfect), and apply it with upgrade head. Never modify the database directly.

The Trap

# TRAP: Money stored as float
price_per_day: Mapped[float] = mapped_column()  # 19.99 becomes 19.989999999999998

# SAFE: Money stored as integer cents
price_per_day_cents: Mapped[int] = mapped_column()  # 1999 is always 1999

Floating point arithmetic produces rounding errors. A stall priced at $19.99 becomes $19.989999999999998 after a multiplication. Store money as integer cents. Display as dollars in the frontend. Stripe uses cents for all amounts. The database should match.

The Cost

Component	Cost
PostgreSQL (Supabase free tier)	$0 (500 MB storage, 50k MAU auth)
Alembic	$0 (open source)
SQLAlchemy	$0 (open source)

At Supabase’s free tier, 500 MB of PostgreSQL storage handles approximately 2-5 million rows of the kind Marketflow produces (markets, stalls, applications, bookings). The paid tier at $25/month provides 8 GB, which handles Marketflow well past the point where revenue justifies the cost.