Building a Prop Firm Cost Analysis Tool with Next.js and SQLite

When I started building PropFirm Key, I quickly realized that comparing proprietary trading firms is far more complex than just looking at sticker prices. A $200 challenge from Firm A might actually cost more than a $300 challenge from Firm B once you factor in activation fees, profit splits, reset costs, and available discounts.

That insight led me to build the True Cost Calculator — a tool that computes the real, all-in cost of prop firm challenges. In this article, I'll walk through the technical implementation: the data model, the calculation engine, the React UI, and the performance considerations that come with processing 370+ challenges in the browser.

Live demo: propfirmkey.com/en/tools/true-cost-calculator

---

The Problem: Why "Price" Isn't "Cost"

Most prop firm comparison sites show the listed price. But traders actually pay more (or less) depending on:

1. Discount codes — Some firms offer 10-90% off through affiliate partnerships
2. Activation fees — Charged after passing the challenge (often $100-500)
3. Profit split — A firm taking 20% of your profits is a hidden cost
4. Account size — A $99 challenge for a $10K account is very different from $99 for $200K
5. Reset fees — Many traders fail and retry multiple times

The True Cost Calculator normalizes all of these into a single comparable metric: cost per dollar of funded capital, expressed as a percentage.

---

Data Model: SQLite with Drizzle ORM

I chose SQLite for this project because the dataset is read-heavy and relatively small (35 firms, 370 challenges, 19 active offers). SQLite eliminates the need for a database server, simplifies Docker deployment, and provides incredible read performance.

Schema Design

Here's the core schema using Drizzle ORM:

// src/lib/db/schema.ts
import { sqliteTable, text, integer, real } from 'drizzle-orm/sqlite-core';

export const firms = sqliteTable('firms', {
  id: text('id').primaryKey(),
  slug: text('slug').notNull(),
  name: text('name').notNull(),
  logo: text('logo'),
  country: text('country').notNull(),
  rating: real('rating').default(0),
  trustpilotRating: real('trustpilot_rating'),
  totalReviews: integer('total_reviews').default(0),
  profitSplit: integer('profit_split').default(80),
  minPrice: integer('min_price').default(99),
  marketType: text('market_type').default('forex'),
  isActive: integer('is_active').default(1),
  // ... ~87 columns total covering features, rules, payouts
});

export const challenges = sqliteTable('challenges', {
  id: text('id').primaryKey(),
  firmId: text('firm_id').notNull().references(() => firms.id),
  name: text('name'),
  accountSize: integer('account_size').notNull(),
  steps: text('steps').notNull(), // "1-step", "2-step", "3-step"
  originalPrice: real('original_price').notNull(),
  discountedPrice: real('discounted_price'),
  activationFee: real('activation_fee'),
  profitSplit: real('profit_split').default(80),
  profitTargetPhase1: real('profit_target_phase1').default(0),
  profitTargetPhase2: real('profit_target_phase2'),
  dailyLossLimit: real('daily_loss_limit').default(0),
  maxLoss: real('max_loss').default(0),
  maxLossType: text('max_loss_type').default('static'),
  minTradingDays: integer('min_trading_days').default(0),
});

export const offers = sqliteTable('offers', {
  id: text('id').primaryKey(),
  firmId: text('firm_id').notNull().references(() => firms.id),
  promoCode: text('promo_code').notNull(),
  discountPercent: real('discount_percent').default(0),
  isExclusive: integer('is_exclusive').default(0),
  isNew: integer('is_new').default(0),
  title: text('title').notNull(),
});

The key design decision here is separating challenges from firms. A single firm might offer 15-20 different challenge configurations (varying account sizes, step counts, and rule sets). This one-to-many relationship is essential for accurate comparisons.

Querying with Drizzle

The server-side data fetching uses Drizzle's relational queries:

// src/lib/db/queries.ts
import { db } from './index';
import { challenges, firms, offers } from './schema';
import { eq, desc } from 'drizzle-orm';

export async function getChallengesWithFirm({ limit = 200 }) {
  return db.query.challenges.findMany({
    limit,
    with: {
      firm: {
        columns: {
          id: true,
          slug: true,
          name: true,
          logo: true,
          countryFlag: true,
          rating: true,
          totalReviews: true,
          isVerified: true,
          marketType: true,
        },
      },
    },
  });
}

export async function getOffersWithFirm(limit = 30) {
  return db.query.offers.findMany({
    limit,
    with: {
      firm: {
        columns: {
          id: true,
          name: true,
          slug: true,
        },
      },
    },
  });
}

Using with for eager loading avoids N+1 queries. The entire dataset (370 challenges + 19 offers) loads in a single round trip, typically under 5ms with SQLite.

---

The True Cost Calculation Engine

This is the heart of the tool. The calculation needs to be client-side because users interact with filters and parameters in real time.

Core Formula

// True Cost = (Effective Price + Activation Fee) / Account Size * 100

interface ChallengeData {
  originalPrice: number;
  discountedPrice?: number;
  activationFee?: number;
  accountSize: number;
  profitSplit: number;
  steps: string;
  dailyLossLimit: number;
  maxLoss: number;
}

interface OfferData {
  firmId: string;
  promoCode: string;
  discountPercent: number;
}

function calculateTrueCost(
  challenge: ChallengeData,
  offer?: OfferData
): {
  effectivePrice: number;
  trueCostPercent: number;
  savingsAmount: number;
  totalCost: number;
} {
  // Step 1: Determine base price
  const basePrice = challenge.discountedPrice || challenge.originalPrice;

  // Step 2: Apply promo discount if available
  const discountMultiplier = offer
    ? (100 - offer.discountPercent) / 100
    : 1;
  const effectivePrice = basePrice * discountMultiplier;

  // Step 3: Add activation fee (charged after passing)
  const activationFee = challenge.activationFee || 0;
  const totalCost = effectivePrice + activationFee;

  // Step 4: Calculate true cost as percentage of funded capital
  const trueCostPercent = (totalCost / challenge.accountSize) * 100;

  // Step 5: Calculate savings vs. original price
  const savingsAmount = challenge.originalPrice - effectivePrice;

  return {
    effectivePrice: Math.round(effectivePrice * 100) / 100,
    trueCostPercent: Math.round(trueCostPercent * 1000) / 1000,
    savingsAmount: Math.round(savingsAmount * 100) / 100,
    totalCost: Math.round(totalCost * 100) / 100,
  };
}

Why True Cost Percentage Matters

Consider two real examples from our database:

Firm	Account Size	Original Price	Discount	Effective Price	Activation Fee	True Cost %
Maven Trading	$10,000	$13	—	$13	$0	0.130%
The5ers	$6,000	$22	5% (PFKEY)	$20.90	$0	0.348%
Blue Guardian	$10,000	$27	50% (PFK)	$13.50	$0	0.135%
FXIFY	$5,000	$39	28% (PFK)	$28.08	$0	0.562%

Without the True Cost calculation, you might think $13 and $27 are wildly different. But with a 50% discount code, Blue Guardian becomes nearly identical to Maven Trading per dollar of funded capital.

---

Server Component Architecture

Next.js 16's App Router with Server Components is perfect for this use case. The data fetching happens server-side, reducing the JavaScript bundle and providing instant SEO-friendly HTML.

// src/app/[locale]/(public)/tools/true-cost-calculator/page.tsx
import { getChallengesWithFirm, getOffersWithFirm } from '@/lib/db/queries';
import { TrueCostBloomberg } from '@/components/tools/TrueCostBloomberg';

export const revalidate = 3600; // ISR: revalidate every hour

export default async function TrueCostCalculatorPage() {
  // Server-side data fetching — zero client-side API calls
  const [challenges, offers] = await Promise.all([
    getChallengesWithFirm({ limit: 200 }),
    getOffersWithFirm(30),
  ]);

  // Transform and serialize data for the client component
  const challengesData = challenges.map((c) => ({
    id: c.id,
    firmId: c.firmId,
    accountSize: c.accountSize,
    steps: c.steps,
    originalPrice: c.originalPrice,
    discountedPrice: c.discountedPrice || undefined,
    activationFee: c.activationFee || undefined,
    profitSplit: c.profitSplit || 80,
    dailyLossLimit: c.dailyLossLimit || 0,
    maxLoss: c.maxLoss || 0,
    firm: c.firm ? {
      id: c.firm.id,
      slug: c.firm.slug,
      name: c.firm.name,
      logo: c.firm.logo || undefined,
      rating: c.firm.rating || 0,
    } : undefined,
  }));

  const offersData = offers.map((o) => ({
    id: o.id,
    firmId: o.firmId,
    promoCode: o.promoCode,
    discountPercent: o.discountPercent || 0,
    title: o.title,
    isExclusive: o.isExclusive || false,
  }));

  return (
    <section className="container py-8">
      <TrueCostBloomberg
        challenges={challengesData}
        offers={offersData}
      />
    </section>
  );
}

The key architectural decisions:

1. Promise.all for parallel data fetching — challenges and offers are independent queries
2. Data transformation at the server boundary — We strip null values and normalize types before serializing to the client
3. revalidate = 3600 — ISR gives us fresh data hourly without hammering SQLite on every request

---

Client-Side Interactive Component

The TrueCostBloomberg component handles filtering, sorting, and the Bloomberg terminal-inspired UI. Here's the simplified structure:

'use client';

import { useMemo, useState } from 'react';
import { useTranslations } from 'next-intl';

interface TrueCostProps {
  challenges: ChallengeData[];
  offers: OfferData[];
}

export function TrueCostBloomberg({ challenges, offers }: TrueCostProps) {
  const t = useTranslations('tools');

  // Filters state
  const [accountSizeRange, setAccountSizeRange] = useState<[number, number]>([0, 500000]);
  const [selectedSteps, setSelectedSteps] = useState<string[]>([]);
  const [selectedMarket, setSelectedMarket] = useState<string>('all');
  const [sortBy, setSortBy] = useState<'trueCost' | 'price' | 'savings'>('trueCost');

  // Build offers lookup map: firmId -> best offer
  const offersMap = useMemo(() => {
    const map = new Map<string, OfferData>();
    for (const offer of offers) {
      const existing = map.get(offer.firmId);
      if (!existing || offer.discountPercent > existing.discountPercent) {
        map.set(offer.firmId, offer);
      }
    }
    return map;
  }, [offers]);

  // Calculate true cost for each challenge and apply filters
  const processedChallenges = useMemo(() => {
    return challenges
      .map((challenge) => {
        const offer = offersMap.get(challenge.firmId);
        const result = calculateTrueCost(challenge, offer);
        return { ...challenge, ...result, offer };
      })
      .filter((c) => {
        if (c.accountSize < accountSizeRange[0] || c.accountSize > accountSizeRange[1]) return false;
        if (selectedSteps.length > 0 && !selectedSteps.includes(c.steps)) return false;
        if (selectedMarket !== 'all' && c.firm?.marketType !== selectedMarket) return false;
        return true;
      })
      .sort((a, b) => {
        switch (sortBy) {
          case 'trueCost': return a.trueCostPercent - b.trueCostPercent;
          case 'price': return a.effectivePrice - b.effectivePrice;
          case 'savings': return b.savingsAmount - a.savingsAmount;
          default: return 0;
        }
      });
  }, [challenges, offersMap, accountSizeRange, selectedSteps, selectedMarket, sortBy]);

  return (
    <div className="glass-card p-6">
      {/* Filters bar */}
      <div className="flex flex-wrap gap-4 mb-6">
        <AccountSizeSlider value={accountSizeRange} onChange={setAccountSizeRange} />
        <StepFilter selected={selectedSteps} onChange={setSelectedSteps} />
        <MarketFilter selected={selectedMarket} onChange={setSelectedMarket} />
        <SortSelect value={sortBy} onChange={setSortBy} />
      </div>

      {/* Results table */}
      <div className="table-premium">
        <table>
          <thead>
            <tr>
              <th>{t('firm')}</th>
              <th>{t('account_size')}</th>
              <th>{t('original_price')}</th>
              <th>{t('discount')}</th>
              <th>{t('effective_price')}</th>
              <th>{t('true_cost')}</th>
              <th>{t('savings')}</th>
            </tr>
          </thead>
          <tbody>
            {processedChallenges.map((c) => (
              <tr key={c.id} className="premium-row">
                <td>
                  <FirmIdentity firm={c.firm} />
                </td>
                <td className="font-data tabular-nums">
                  ${c.accountSize.toLocaleString()}
                </td>
                <td className="font-data tabular-nums text-muted-foreground line-through">
                  ${c.originalPrice}
                </td>
                <td>
                  {c.offer && (
                    <span className="savings-badge">
                      -{c.offer.discountPercent}% ({c.offer.promoCode})
                    </span>
                  )}
                </td>
                <td className="font-data tabular-nums text-status-success">
                  ${c.effectivePrice}
                </td>
                <td className="font-data tabular-nums font-bold">
                  {c.trueCostPercent.toFixed(3)}%
                </td>
                <td className="font-data tabular-nums text-status-success">
                  {c.savingsAmount > 0 && `$${c.savingsAmount}`}
                </td>
              </tr>
            ))}
          </tbody>
        </table>
      </div>
    </div>
  );
}

Performance Optimization with useMemo

Processing 370 challenges with calculations, filtering, and sorting on every render would be wasteful. The useMemo hook ensures we only recompute when the inputs actually change:

1. offersMap — Rebuilt only when offers array changes (effectively never after initial render)
2. processedChallenges — Recalculated only when filters change or the base data changes

In practice, this means the calculation runs ~5-10 times during a typical user session, not 370 times per keystroke.

---

Internationalization: 10 Locales

PropFirm Key serves traders globally, so every page — including the True Cost Calculator — is available in 10 languages: English, French, Spanish, German, Portuguese, Italian, Russian, Chinese, Japanese, and Dutch.

We use next-intl with URL-based routing:

// src/i18n/config.ts
export const locales = ['en', 'fr', 'es', 'de', 'pt', 'it', 'ru', 'zh', 'ja', 'nl'] as const;
export type Locale = (typeof locales)[number];

// URL structure: /en/tools/true-cost-calculator, /fr/tools/true-cost-calculator, etc.

The translation files contain all UI strings:

// src/messages/en.json (tools namespace)
{
  "tools": {
    "true_cost_meta_title": "True Cost Calculator — Compare Real Prop Firm Prices",
    "true_cost_h1_prefix": "Calculate the",
    "true_cost_h1_highlight": "True Cost",
    "true_cost_live_badge": "{count} challenges analyzed live",
    "firm": "Firm",
    "account_size": "Account Size",
    "original_price": "Original Price",
    "effective_price": "Effective Price",
    "true_cost": "True Cost %",
    "savings": "Savings"
  }
}

Server Components use getTranslations() (async), while Client Components use the useTranslations() hook. This separation is critical — mixing them causes hydration mismatches.

---

Docker Deployment: SQLite in Production

Running SQLite in Docker requires careful volume management:

# Dockerfile
FROM node:20-alpine AS runner
WORKDIR /app

COPY --from=builder /app/.next/standalone ./
COPY --from=builder /app/.next/static ./.next/static
COPY --from=builder /app/public ./public

# SQLite WAL mode needs the DIRECTORY mounted, not the file
# This prevents WAL corruption from cross-filesystem access
VOLUME ["/app/data"]

EXPOSE 3000
CMD ["node", "server.js"]

# docker-compose.yml
services:
  app:
    build: .
    ports:
      - "3001:3000"
    volumes:
      - ./data:/app/data           # SQLite directory (NOT file)
      - static-chunks:/app/.next/static  # Persistent chunks across rebuilds
    restart: unless-stopped

volumes:
  static-chunks:

The SQLite WAL Lesson (Learned the Hard Way)

We had a production incident where accessing the SQLite database from both the host and the container simultaneously corrupted the WAL (Write-Ahead Log). The fix: always access the database through docker exec, never directly from the host while the container is running.

# WRONG — causes WAL corruption
sqlite3 data/propfirmkey.db "SELECT * FROM firms"

# CORRECT — same process as the app
docker exec propfirmkey-app sqlite3 /app/data/propfirmkey.db "SELECT * FROM firms"

---

SEO: Making a Tool Page Rank

A tool page needs extra SEO attention because search engines can't "use" the interactive tool. We compensate with:

1. Server-rendered HTML — The initial table renders with real data before JavaScript loads
2. Structured data — BreadcrumbList JSON-LD for navigation context
3. Hreflang tags — Pointing to all 10 locale variants
4. Static prose sections — Below the tool, we explain the methodology in detail (purely server-rendered)

// Metadata with full SEO configuration
export async function generateMetadata({ params }): Promise<Metadata> {
  const { locale } = await params;
  const t = await getTranslations({ locale, namespace: 'tools' });
  const canonical = `https://propfirmkey.com/${locale}/tools/true-cost-calculator`;

  return {
    title: t('true_cost_meta_title'),
    description: t('true_cost_meta_description'),
    keywords: ['prop firm true cost', 'prop firm calculator', 'cheapest prop firm'],
    openGraph: {
      title: t('true_cost_meta_title'),
      description: t('true_cost_meta_description'),
      url: canonical,
      images: [{ url: 'https://propfirmkey.com/og-image.png', width: 1200, height: 630 }],
    },
    alternates: {
      canonical,
      languages: generateHreflangLinks('/tools/true-cost-calculator').languages,
    },
  };
}

---

Performance Results

The current Lighthouse scores for the True Cost Calculator page:

Metric	Score
Performance	92
Accessibility	97
Best Practices	95
SEO	100

Key factors:

• Zero API calls from the client — all data is embedded in the server-rendered HTML via props
• content-visibility: auto on below-the-fold sections for paint optimization
• tabular-nums and font-data (JetBrains Mono) — pre-loaded to prevent layout shift on numeric data
• ISR with revalidate = 3600 — cached at the edge, rebuilt hourly

---

Key Takeaways

1. SQLite is production-ready for read-heavy, single-server applications. Our 370-challenge dataset loads in under 5ms.

2. Server Components + Client interactivity is the sweet spot. Fetch data server-side, calculate client-side. Best of both worlds.

3. True cost > listed price. If you're building any comparison tool, think about what "cost" really means to your users. The compound metric (cost/funded capital) revealed insights that raw prices obscured.

4. i18n from day one. Retrofitting 10 locales is painful. We started with next-intl early and now serve traders in their native language across 10 locales.

5. SQLite + Docker needs care. Mount the directory, not the file. Never access from both host and container. Use WAL mode but respect its constraints.

---

The True Cost Calculator is live at propfirmkey.com/en/tools/true-cost-calculator — processing 370+ challenges from 35 firms with real-time discount calculations. If you're building fintech tools with Next.js, I hope this architectural breakdown was useful.

The full platform at PropFirm Key covers firm comparisons, detailed reviews, and the latest discount codes for prop trading challenges.

---

Built with Next.js 16, TypeScript, SQLite, Drizzle ORM, and Tailwind CSS. Deployed on Docker with Caddy reverse proxy.