Subscription and Tenant Tracking Tables

The Feature

Marketflow enforces the freemium limit: markets with more than 20 active vendors require a paid subscription. The database tracks each organizer’s subscription status and provides an efficient query to count active vendors for limit enforcement.

The Decision

Subscription state lives in both Stripe and the local database. Stripe is the source of truth for billing: payment status, subscription period, invoice history. The local database mirrors the subscription tier so that API endpoints can enforce limits without calling the Stripe API on every request. Stripe webhooks keep the local state synchronized (Chapter 8 implements the webhook handlers).

The Implementation

Organizer Profile

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

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

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


class SubscriptionTier(str, enum.Enum):
    FREE = "free"
    PAID = "paid"


class OrganizerProfile(UUIDMixin, TimestampMixin, Base):
    __tablename__ = "organizer_profiles"

    user_id: Mapped[uuid_mod.UUID] = mapped_column(unique=True, index=True)
    display_name: Mapped[str] = mapped_column(String(200))
    email: Mapped[str] = mapped_column(String(254))
    subscription_tier: Mapped[SubscriptionTier] = mapped_column(
        Enum(SubscriptionTier),
        default=SubscriptionTier.FREE,
    )
    stripe_customer_id: Mapped[str | None] = mapped_column(
        String(255), unique=True
    )
    stripe_subscription_id: Mapped[str | None] = mapped_column(
        String(255), unique=True
    )

    markets: Mapped[list["Market"]] = relationship(
        back_populates="organizer",
        foreign_keys="Market.organizer_id",
    )

The stripe_customer_id and stripe_subscription_id are set when the organizer creates a Stripe checkout session (Chapter 8). Free-tier organizers have both set to None.

Active Vendor Count Query

# backend/app/services/vendor_limits.py
import uuid

from sqlalchemy import func, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.application import Application, ApplicationStatus
from app.models.booking import Booking
from app.models.market_day import MarketDay


async def count_active_vendors(
    db: AsyncSession,
    market_id: uuid.UUID,
) -> int:
    """Count vendors with at least one booking on a current or future market day."""
    result = await db.execute(
        select(func.count(func.distinct(Booking.vendor_id)))
        .join(MarketDay, Booking.market_day_id == MarketDay.id)
        .where(
            MarketDay.market_id == market_id,
            MarketDay.date >= func.current_date(),
            MarketDay.is_cancelled.is_(False),
        )
    )
    return result.scalar_one()


async def can_add_vendor(
    db: AsyncSession,
    market_id: uuid.UUID,
    organizer_tier: str,
) -> bool:
    """Check if a market can accept another vendor."""
    if organizer_tier == "paid":
        return True

    active_count = await count_active_vendors(db, market_id)
    return active_count < 20

The query counts distinct vendor IDs across bookings for non-cancelled future market days. A vendor who applied but was never assigned a stall does not count. A vendor who had a booking last month but has no upcoming bookings does not count. The 20-vendor limit applies to vendors who are actively booked for future market days.

Enforcing the Limit

# backend/app/services/booking_service.py
from app.services.vendor_limits import can_add_vendor


async def create_booking(
    db: AsyncSession,
    market_id: uuid.UUID,
    stall_id: uuid.UUID,
    vendor_id: uuid.UUID,
    market_day_id: uuid.UUID,
    organizer: OrganizerProfile,
) -> Booking:
    # Check vendor limit before creating booking
    if not await can_add_vendor(db, market_id, organizer.subscription_tier):
        raise VendorLimitExceededError(
            "Free tier limit reached. Upgrade to add more vendors."
        )

    # Check stall is not already booked for this market day
    existing = await db.execute(
        select(Booking).where(
            Booking.stall_id == stall_id,
            Booking.market_day_id == market_day_id,
        )
    )
    if existing.scalar_one_or_none():
        raise StallAlreadyBookedError(
            f"Stall is already booked for this market day"
        )

    booking = Booking(
        stall_id=stall_id,
        vendor_id=vendor_id,
        market_day_id=market_day_id,
        fee_cents=0,  # Set by organizer later
    )
    db.add(booking)
    await db.commit()
    await db.refresh(booking)
    return booking

The limit check runs before the booking is created. If the limit is exceeded, the booking is rejected with a clear error message that tells the organizer how to resolve it (upgrade). The check is in the service layer, not the router, so it applies regardless of how the booking is created.

The Trap

# TRAP: Counting ALL applications as active vendors
async def count_active_vendors(db: AsyncSession, market_id: uuid.UUID) -> int:
    result = await db.execute(
        select(func.count(Application.id))
        .where(
            Application.market_id == market_id,
            Application.status == ApplicationStatus.ACCEPTED,
        )
    )
    return result.scalar_one()

# This counts vendors who were accepted months ago but have no upcoming bookings.
# An organizer who accepted 25 vendors last season and only has 10 returning
# this season is told they need to upgrade. That is wrong and frustrating.

# SAFE: Count vendors with active bookings on future market days
# (implementation shown above)

The definition of “active vendor” directly affects when organizers hit the paywall. A generous definition (vendors with future bookings) is fair and does not penalize organizers for historical data. A strict definition (all accepted vendors ever) pushes organizers to the paid tier prematurely and generates support tickets.

The Cost

Query	Estimated Time (1000 bookings)	Estimated Time (100,000 bookings)
Active vendor count	<5ms	~50ms

The active vendor count query is fast because it filters on future dates (small result set) and counts distinct vendor IDs. An index on market_days.date and market_days.market_id keeps it fast as the data grows. At 100,000 bookings, the query is still under the threshold where caching would be justified.