Why We Chose Supabase Over Firebase for AI Apps (And How Our Template Uses It)

Choosing between Firebase and Supabase for an AI template isn't just about personal preference. Your database choice affects everything: how you query data, what your costs look like, how easily you can scale, and whether you can even build the AI features you want.

We picked Supabase for TemplateAI. And if we had to choose again today, we'd make the same decision.

In this post, I'll explain why Supabase beats Firebase specifically for AI applications, walk through the key differences that matter for embeddings and vector search, and show you exactly how our template uses PostgreSQL + Supabase to power production-ready AI features.

Firebase vs Supabase: The AI App Perspective

Let's be honest about both options.

Firebase's strengths are real:

• Real-time synchronization works out of the box
• Deep integration with Google's ecosystem (Gemini, Vertex AI)
• Turnkey AI features through Firebase Genkit and built-in AI assistants
• Zero configuration for mobile apps
• Proven at massive scale

But Firebase has critical limitations for AI apps:

No mature vector support. Firestore vector search launched in 2024, making it relatively new compared to PostgreSQL's pgvector, which has been battle-tested in production for over two years. When you're building semantic search or RAG features, you want proven technology.

NoSQL complexity. One Reddit developer put it perfectly: "Firestore is amazing... until you try to run a complex query and realize you need to restructure your entire data model." AI apps need to join user data with embeddings with subscription status. NoSQL makes this painful.

Pricing by request. Firebase charges per document read and write. Vector searches count as reads. With high-volume AI apps making thousands of similarity searches daily, costs add up fast.

Vendor lock-in. Firebase is proprietary to Google. You can't self-host it, you can't easily migrate away, and you're dependent on Google's pricing decisions.

Query limitations. Firestore doesn't support joins. When you need to filter vector search results by user subscription tier or creation date, you're stuck with multiple queries and client-side filtering.

Supabase's advantages for AI are compelling:

pgvector is battle-tested. PostgreSQL's vector extension is used in production by Notion, Replit, Scale AI, and thousands of other companies. It's mature, performant, and well-documented.

SQL power. Complex queries, joins, and aggregations work in a single statement. You can query embeddings while filtering by user properties, dates, metadata—all in one go.

Open source. You can self-host Supabase, migrate to another PostgreSQL host, or run it locally for development. No vendor lock-in.

Better cost structure. Supabase charges for storage and compute, not individual operations. For AI workloads with high read volumes, this is dramatically cheaper.

Relational data model. AI apps need structured relationships: users have conversations, conversations have messages, users have subscriptions, embeddings belong to documents. PostgreSQL handles this naturally.

The tipping point for us was a simple use case: "Find similar documents created by premium users this month."

In Firebase, you'd need:

1. Query Firestore for vector similarity
2. Fetch user data for each result
3. Check subscription status for each user
4. Filter by date on the client
5. Combine everything in application code

In PostgreSQL with Supabase:

SELECT d.content, d.metadata, u.email
FROM documents d
JOIN auth.users u ON d.user_id = u.id
JOIN customers c ON u.id = c.id
WHERE c.has_access = true
AND d.created_at > NOW() - INTERVAL '30 days'
AND d.embedding <=> query_embedding < 0.5
ORDER BY d.embedding <=> query_embedding
LIMIT 10;

One query. No client-side filtering. No multiple round trips. That's the power of relational databases for AI apps.

PostgreSQL + pgvector: The Vector Search Advantage

If you're building AI features, you need to store and query vector embeddings. This is non-negotiable for semantic search, RAG (retrieval-augmented generation), recommendation engines, and similar document finding.

What is pgvector? It's a PostgreSQL extension that adds vector data types and similarity search operators. It's the same technology powering semantic search in production at major tech companies.

Here's why pgvector beats Firestore's vector search:

How TemplateAI uses pgvector:

At build time, we generate embeddings for your documentation:

1. The src/utils/generate-embeddings.ts script reads files from your docs/ folder
2. Documents are split into chunks (using LangChain's RecursiveCharacterTextSplitter)
3. Each chunk gets embedded using OpenAI's text-embedding-3-small model (5x cheaper than previous models)
4. Embeddings are stored in the documents table with a vector(1536) column

At runtime, when a user asks a question:

1. The question is converted to an embedding
2. Our custom match_documents() function performs cosine similarity search
3. The most relevant document chunks are retrieved
4. These chunks are injected as context into the GPT prompt
5. The AI generates a response based on your actual documentation

Here's the actual PostgreSQL function from our template:

-- supabase/migrations/20231212154829_add_pgvector_search.sql
create function match_documents (
  query_embedding vector(1536),
  match_count int DEFAULT null,
  filter jsonb DEFAULT '{}'
) returns table (
  id bigint,
  content text,
  metadata jsonb,
  similarity float
)
language plpgsql
as $$
begin
  return query
  select
    id,
    content,
    metadata,
    1 - (documents.embedding <=> query_embedding) as similarity
  from documents
  where metadata @> filter
  order by documents.embedding <=> query_embedding
  limit match_count;
end;
$$;

The <=> operator is pgvector's cosine distance operator. The metadata @> filter part lets you filter by document properties before searching—something Firestore struggles with.

Why this matters: You can build "ChatGPT for your docs" in 30 minutes with TemplateAI. Just run npm run embeddings, and your entire documentation becomes semantically searchable. No need for a separate vector database like Pinecone or Weaviate. Everything lives in one Postgres database.

Real-World Database Architecture: What TemplateAI Actually Stores

Let's look at the actual tables our template creates and why each one matters for AI applications.

Authentication Tables

auth.users (built-in): Supabase handles all authentication automatically. Magic links, Google OAuth, password-based auth—all managed by Supabase without you writing auth code.

profiles: Custom user data that extends the basic auth table. Stores username, full name, avatar URL, and website. When a user signs up, a database trigger automatically creates their profile entry. Row Level Security ensures users can view all profiles but only edit their own.

File: supabase/migrations/20231212222101_user_management_starter.sql

AI Feature Tables

documents: The heart of vector search. Each row contains:

• content: The actual text chunk
• metadata: JSON with source file, chunk index, etc.
• embedding: A 1536-dimensional vector

This table powers the ChatModal component that lets users search your documentation semantically.

File: supabase/migrations/20231212154829_add_pgvector_search.sql

SDXL_images: Stores AI-generated images from Replicate. Each row tracks:

• prompt: What the user asked for
• image_url: The generated image URL
• model: Which model was used
• predict_time: How long generation took
• user_id: Who created it

This enables the image gallery feature where users can browse their generation history.

File: supabase/migrations/20231212155109_add_sdxl_images.sql

chats: Conversation history storage. Uses a flexible JSONB payload column to store different types of chat data. Row Level Security policies make chats private by default but allow public reading if a chat has a sharePath property set.

File: supabase/migrations/20231227001653_create_chats_table.sql

Monetization Tables

customers: Maps Supabase user IDs to Stripe customer IDs. Includes a critical has_access boolean flag you can use to paywall features. This table is completely private—even the user can't read their own row, preventing manipulation.

products & prices: Automatically synced from Stripe via webhooks. These tables are public (read-only) so you can display pricing on your landing page directly from your database.

subscriptions: Full subscription lifecycle data. Status, billing periods, cancellation dates, trial information—everything you need to check if a user has an active subscription and what features they should access.

File: supabase/migrations/20231213031844_create_stripe_tables.sql

The Power of Relational Queries

Here's where PostgreSQL shines. Want to get premium users' recent AI-generated images?

SELECT u.email, i.image_url, i.created_at
FROM SDXL_images i
JOIN auth.users u ON i.user_id = u.id
JOIN customers c ON u.id = c.id
WHERE c.has_access = true
AND i.created_at > NOW() - INTERVAL '7 days'
ORDER BY i.created_at DESC;

Try that in Firestore. You'd need three separate queries (images, users, customers) and then manually join them in your application code. With PostgreSQL, it's one efficient query executed at the database layer.

Storage Buckets

Beyond tables, Supabase provides S3-compatible storage with two buckets in our template:

• avatars: Profile pictures (public read, anyone can upload)
• images: User uploads for AI processing (authenticated uploads only)

Files: supabase/migrations/20231212222101_user_management_starter.sql and supabase/migrations/20231219200400_add_images_storage_bucket.sql

Migration Setup

All of this database architecture is set up for you via 6 migration files in /supabase/migrations/. When you clone the template, you just run:

npx supabase db push

Your entire schema deploys in seconds. No manual table creation, no clicking through admin panels, no writing CREATE TABLE statements yourself.

Row Level Security: Multi-Tenancy Built In

Firebase has Security Rules. PostgreSQL has Row Level Security (RLS). RLS is better.

Here's why: RLS enforces access control at the database level. Even if your API keys leak, users can only access their own data. Security Rules in Firebase are enforced at the application level, which means they're more vulnerable to misconfiguration and bypass.

Our template's RLS patterns:

-- Users can only see their own chats
create policy "Allow full access to own chats"
on chats
for all
to authenticated
using (auth.uid() = user_id);

This policy means when a user queries the chats table, PostgreSQL automatically filters to only show chats where user_id matches the authenticated user's ID. You don't write any filtering logic in your application code—the database handles it.

Generated images are private per user:

-- Similar pattern applied to SDXL_images table

Subscription data is strictly private:

create policy "Can only view own subs data"
on subscriptions
for select
using (auth.uid() = user_id);

Why this matters:

• Multi-tenant by default. No manual user ID filtering in your API routes.
• Security enforced at the deepest layer possible.
• The Supabase client respects RLS automatically in both browser and server contexts.
• You can't accidentally expose another user's data by forgetting a WHERE clause.

Firestore Security Rules comparison:

• Application-level enforcement (less secure)
• More verbose syntax for complex rules
• Easier to make mistakes that expose data
• No SQL-level guarantees

With PostgreSQL RLS, your database is secure by design. With Firestore, you're writing security logic in a separate rules file and hoping you got it right.

Cost Comparison for AI Apps

Let's talk about money.

Typical AI app usage patterns:

• High read volume (fetching chat history, browsing images, searching docs)
• Moderate writes (new messages, generated content, embeddings)
• Large datasets (embeddings can be megabytes per thousand documents)

Firebase pricing model:

• Charged per document read and write
• Vector similarity searches count as reads (potentially many)
• Free tier: 50K reads/day, then $0.06 per 100K reads
• Storage: $0.18/GB/month

Supabase pricing model:

• Free tier: 500MB database, 1GB file storage, 2GB bandwidth/month
• Charged by database size and compute, not operations
• Unlimited reads and writes within compute limits
• Storage: Included in database size

Real example for a vector search app:

• 10,000 daily vector searches across 1,000 documents
• Each search might read 10-20 documents for comparison

Firebase cost:

• 10K searches × 15 avg reads × 30 days = 4.5M reads/month
• (4.5M - 1.5M free) / 100K × $0.06 = $1.80/month just for vector searches
• Plus storage for embeddings (~$0.02/month for 100MB)
• Total: ~$1.82/month

Supabase cost:

• Same 10K daily searches
• Embeddings stored in database (~100MB)
• Well within free tier limits
• Total: $0/month until you exceed 500MB database

As you scale:

• Firebase: Cost scales linearly with read/write volume
• Supabase: Cost scales with storage and compute resources, queries stay unlimited

For AI applications with high query volumes and large embedding datasets, Supabase's pricing model is significantly more favorable.

Developer Experience: Why We Actually Enjoy Using Supabase

Beyond features and pricing, there's developer experience. Here's what makes Supabase a joy to work with:

Local development: Run a complete Supabase environment locally with Docker:

npx supabase start

You get a local PostgreSQL database, local storage, local auth, and even a local Studio UI. Test everything offline. Firebase requires cloud connectivity for development.

Type safety: Generate TypeScript types directly from your database schema:

npx supabase gen types typescript --local > types/database.ts

Now your queries are type-safe. You get autocomplete for table names, column names, and relationships. With Firestore, you're manually writing type definitions.

SQL Studio: Supabase Studio gives you a visual interface to browse tables, run SQL queries, see relationships, and inspect data. The Firebase console makes you navigate collections one at a time with no SQL query ability.

CLI power:

npx supabase db diff     # See schema changes
npx supabase db reset    # Reset local database
npx supabase db push     # Push migrations to prod

Everything is scriptable and version-controlled.

The template's setup:

• src/utils/supabase-client.ts: Browser client for React components
• src/utils/supabase-server.ts: Server-side helpers for API routes and server components
• src/utils/generate-embeddings.ts: Script to generate and store vector embeddings

Migration workflow:

1. Write SQL in a new file in /supabase/migrations/
2. Test locally with npx supabase start
3. Push to production with npx supabase db push
4. No GUI clicking, everything is code

This workflow makes database changes reviewable in pull requests, testable before production, and completely reproducible across environments.

The Trade-offs: When to Choose Firebase Instead

We need to be honest: Firebase is better for certain use cases.

Choose Firebase if you're building:

• Mobile-first apps that need offline sync and real-time collaboration (like Google Docs)
• Real-time dashboards where you need instant updates across clients
• Apps in the Google ecosystem where you're already using GCP, Vertex AI, and other Google services
• Projects with no SQL experience on your team (Firebase's NoSQL is more approachable for beginners)
• Rapid prototypes where you need zero setup and can deploy in minutes

We chose Supabase for TemplateAI because:

• AI apps require complex queries (joins, filters, aggregations across multiple tables)
• Vector search is a core feature, and pgvector is more mature and battle-tested
• Cost predictability matters (storage-based pricing > operation-based for our use case)
• We prefer SQL over NoSQL for structured, relational data
• The ability to self-host or migrate away is valuable for long-term flexibility

No regrets. The template handles everything we need. The SQL power, type safety, and cost structure make Supabase the right choice for AI applications. The initial setup (minimal as it is) pays dividends in every feature you build afterward.

Conclusion

Choosing between Firebase and Supabase isn't about which is "better" overall—it's about which is better for your specific use case.

For AI applications, Supabase wins. The combination of PostgreSQL's relational power, pgvector's mature vector search, cost-effective pricing for high-volume queries, and RLS-based security creates a foundation that makes building AI features straightforward rather than painful.

What you get with TemplateAI:

• Production-ready Supabase schema with all migrations included
• pgvector setup for semantic search and RAG
• Stripe integration tables for monetization
• Row Level Security policies built in
• Authentication tables with automatic profile creation
• Storage buckets with proper access controls

Getting started is simple:

git clone template-ai
npm install
npx supabase db push

Your database is deployed. Start building AI features.

Next steps:

• Database setup guide
• Vector search tutorial
• Authentication documentation

Ready to build with Supabase and PostgreSQL? Get TemplateAI.