Batch 05: Databases & SQL for AI | Codebitbybit × Tavro Studio

For AI Engineers Who Build Real Systems

Every AI product runs on a database. User data, model outputs, embeddings, conversation history, experiment logs — all stored, queried, and retrieved using SQL and specialized data stores. This batch teaches you to build the data layer every AI system needs — from your first SELECT to a production RAG pipeline with vector search.

From SELECT * to Vector Search

A complete journey through modern database engineering — built specifically for AI developers. Every chapter connects database skills directly to building AI products.

World of Databases

4 eps · ~1.5 hrs · Relational theory to SQL fundamentals

SQL Mastery

8 eps · ~3.5 hrs · SELECT to Window Functions

Design & Optimization

5 eps · ~2.2 hrs · Schema, Indexes, EXPLAIN

NoSQL & Beyond

4 eps · ~1.8 hrs · MongoDB, Redis, Graph DBs

Vector Databases

5 eps · ~2.5 hrs · Semantic search · RAG architecture

Supabase & Production

4 eps · ~2 hrs · AI product data stack + Capstone

Chapter 01 — The World of Databases

Before writing your first query, you need to understand what a database actually is, why it was invented, and what relational theory means. This chapter connects database fundamentals to the AI context — showing why every AI system needs a well-designed data layer.

Every beginner AI student loads data from CSV files. When your dataset grows to millions of rows, or when 1,000 users query your AI simultaneously, CSV files collapse. This episode shows exactly why — concurrency, data integrity, query performance — and introduces databases as the architectural solution.

Edgar Codd invented the relational model at IBM in 1970. Tables as mathematical relations, primary keys as unique identifiers, foreign keys as relationships between tables, referential integrity constraints, and NULL handling. We animate what happens when you violate a foreign key constraint — and why databases prevent it automatically.

SQL was invented in 1974 at IBM. Its four statement categories: DQL (query), DML (data manipulation), DDL (schema), DCL (permissions). SQL dialects: PostgreSQL, MySQL, SQLite, BigQuery — and what's different between them. We write our very first query and understand the SQL execution order (FROM → WHERE → SELECT, not left-to-right).

Install PostgreSQL (Mac/Windows/Linux), SQLite for lightweight learning, DBeaver as a visual SQL client, psql command-line basics, create your first database and first table, insert your first rows, and run your first SELECT query. Every command explained line by line — no assumed knowledge.

Chapter 02 — SQL Mastery

From your first SELECT to the most powerful SQL features: window functions, CTEs, complex JOINs, and analytical queries. This is the deepest SQL chapter — 8 episodes covering every clause, operator, and pattern a professional data engineer uses daily. Every query has an AI application.

SELECT columns vs *, WHERE clauses (comparison, logical, LIKE, IN, BETWEEN, IS NULL), ORDER BY (ASC/DESC, multi-column), LIMIT and OFFSET for pagination, column aliases with AS, and the SQL execution order (FROM → WHERE → SELECT → ORDER BY). Every clause animated with real result sets.

Aggregate functions (COUNT, SUM, AVG, MIN, MAX, STRING_AGG), GROUP BY to collapse groups, HAVING to filter groups (vs WHERE which filters rows), ROLLUP and CUBE for multi-level aggregation, and FILTER clause for conditional aggregates. We compute class distribution statistics for an AI training dataset — a real preprocessing query.

INNER JOIN (intersection), LEFT JOIN (all left + matching right), RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, self-joins, and multi-table JOINs. Animated Venn diagrams for every type. The "LEFT JOIN + IS NULL" pattern for finding missing records. We write a query that finds all messages that haven't been embedded yet — a real AI data pipeline query.

Subqueries in SELECT, FROM, and WHERE clauses, correlated subqueries, EXISTS and NOT EXISTS, Common Table Expressions (WITH clause), recursive CTEs for hierarchical data (org charts, knowledge graphs), and why CTEs improve readability over nested subqueries. We build a multi-step ML experiment analysis query using CTEs.

OVER() clause, PARTITION BY, ORDER BY within window, ROW_NUMBER / RANK / DENSE_RANK, LAG and LEAD (access previous/next rows), running totals with SUM() OVER, rolling averages, FIRST_VALUE / LAST_VALUE / NTH_VALUE, NTILE for percentiles. We compute model improvement over training runs — a time-series analysis of AI experiment results.

INSERT single and bulk rows (INSERT ... VALUES vs INSERT ... SELECT), UPDATE with WHERE (the WHERE-less UPDATE disaster), DELETE safely, RETURNING clause (get inserted/updated rows back), UPSERT with ON CONFLICT DO UPDATE, and soft deletes (deleted_at pattern). We build an embedding insertion function for a RAG pipeline.

DATE/TIME functions (NOW, DATE_TRUNC, EXTRACT, AGE, intervals), CASE expressions for conditional columns, string functions for text feature engineering, CROSSTAB for pivot tables, generate_series for time-series gaps, and computed columns as ML features. We engineer a complete user feature table for an AI recommendation model — entirely in SQL.

JSON and JSONB types in PostgreSQL, ->, ->>, #>, #>> operators for JSON path access, containment operators (@>, < @), JSON indexing with GIN, array types, text search (tsvector/tsquery), and generated columns. We store and query raw AI API responses (Claude, OpenAI format) entirely in PostgreSQL using JSONB.

Chapter 03 — Database Design & Optimization

Writing SQL is one skill. Designing the schema it runs on — and making sure queries are fast — is a completely different discipline. This chapter teaches you to think in data models, normalize schemas, build the right indexes, and diagnose slow queries before they become production incidents.

Entity-Relationship (ER) diagrams with crow's foot notation, identifying entities and relationships (one-to-one, one-to-many, many-to-many), translating ER diagrams to SQL CREATE TABLE statements, data types (UUID, TEXT, JSONB, TIMESTAMPTZ, VECTOR), and constraints (NOT NULL, UNIQUE, CHECK, DEFAULT). We design a complete AI chat application schema from scratch.

1NF (atomic values, no repeating groups), 2NF (no partial dependencies on composite keys), 3NF (no transitive dependencies — store facts about one thing per table), when to de-normalize for performance, junction tables for many-to-many relationships, and BCNF. Every form shown with a real AI application schema before and after.

How B-tree indexes work (animated — from O(n) scan to O(log n) lookup), when to add indexes (query columns in WHERE, JOIN ON, ORDER BY), composite indexes and column order, partial indexes, covering indexes, index types (HASH, GIN, GiST, BRIN), index maintenance overhead, and the critical question: why not index everything?

Atomicity (all-or-nothing), Consistency (data stays valid), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes), SAVEPOINT for nested transactions, isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE), deadlocks and how to prevent them, and optimistic vs pessimistic locking.

EXPLAIN vs EXPLAIN ANALYZE, reading query plan nodes (Seq Scan, Index Scan, Nested Loop, Hash Join, Merge Join), understanding cost estimates vs actual time, identifying the bottleneck (most expensive node), fixing slow queries with indexes, query rewriting strategies, pg_stat_statements for finding slow queries in production, and autovacuum basics.

Chapter 04 — NoSQL & Alternative Data Stores

SQL databases are excellent — but not for everything. Document databases, key-value stores, and graph databases each solve specific problems that SQL handles poorly. AI applications frequently need all of these: MongoDB for flexible schemas, Redis for caching, Neo4j for knowledge graphs. Know when to use what.

CAP theorem (Consistency, Availability, Partition Tolerance — pick 2), BASE vs ACID, document stores vs key-value vs column-family vs graph databases, when to choose NoSQL (flexible schema, horizontal scale, unstructured data), and the misconception that NoSQL replaces SQL. We use a decision framework for 5 real AI application data scenarios.

BSON document model, collections vs tables, CRUD operations (insertOne/insertMany/find/updateOne/deleteOne), query operators ($gt, $in, $regex, $exists), the aggregation pipeline ($match, $group, $sort, $project, $lookup), MongoDB indexes, and schema design patterns for flexible AI response storage (different models have different response shapes).

Redis data structures (strings, hashes, lists, sets, sorted sets), SET/GET with TTL (time-to-live) for caching, the cache-aside pattern, Redis pub/sub for event streaming, Redis Streams for AI response streaming, rate limiting with sorted sets (preventing API abuse), and session storage for AI chatbots. Redis runs at microsecond latency — 1,000× faster than PostgreSQL for simple lookups.

Graph model (nodes, relationships, properties), Cypher query language basics (MATCH, WHERE, RETURN, CREATE, MERGE), pattern matching, variable-length paths, graph algorithms (shortest path, centrality, community detection), and when graph databases beat SQL for relationship-heavy queries. We build a simple AI knowledge graph for a RAG system.

Chapter 05 — Vector Databases: The AI-Native Data Layer

This is the chapter that makes Batch 05 unlike any other database course. Vector databases store high-dimensional embeddings and search them by semantic similarity — enabling RAG, semantic search, recommendation systems, and image retrieval. This is the data infrastructure that makes modern AI products possible.

The fundamental problem with keyword search (LIKE, full-text) — it misses synonyms and related concepts. Embeddings as dense vector representations of meaning (text, images, audio). Cosine similarity and Euclidean distance as measures of semantic closeness. How vector databases enable "search by meaning." The use cases: RAG, recommendation, deduplication, anomaly detection.

Embedding models (OpenAI text-embedding-3-large: 3072D, Sentence Transformers: 384D, Google gecko: 768D), how to generate embeddings via API in Python, storing them in PostgreSQL with pgvector (VECTOR type), querying with < => (cosine), < -> (L2), < #> (inner product) operators, and performance characteristics. Full Python pipeline: text → embed → store.

Exact nearest neighbor search is O(n) — too slow at scale. Approximate Nearest Neighbor (ANN): IVFFlat (K-means clustering → search nearest centroid's cluster), HNSW (Hierarchical Navigable Small World graphs — navigates a layered graph to find approximate neighbors in O(log n)). Trade-off: speed vs recall. Configuring HNSW parameters (m, ef_construction, ef_search).

Full comparison of the vector database landscape: ChromaDB (open source, embedded, perfect for prototyping), Pinecone (managed cloud, production scale), Weaviate (graph + vectors), Qdrant (Rust, high performance), pgvector (PostgreSQL extension — SQL + vectors in one), and Chroma vs Pinecone for different stages of an AI project. With cost and scaling analysis.

Complete RAG pipeline: chunk documents (fixed-size with overlap), embed chunks with OpenAI/Anthropic API, store in pgvector, embed user query, similarity search for top-K chunks, assemble context prompt, call LLM with retrieved context, store response. Metadata filtering (filter by date, author, category before vector search), hybrid search (vector + full-text), and re-ranking.

Chapter 06 — Supabase & Production AI Data Stack

Supabase is the perfect database platform for AI products: PostgreSQL + pgvector + real-time + auth + storage + API generation — all open source. This chapter teaches you to deploy a production AI data layer from scratch, culminating in a fully working AI knowledge base application.

Supabase overview (PostgreSQL + PostgREST auto-API + GoTrue auth + Realtime + Storage + Edge Functions), create a project, connect with Python (supabase-py), the auto-generated REST API (no backend code needed for CRUD), enabling the pgvector extension (one click), the Supabase Studio UI, and pricing for different AI product scale stages.

Complete schema design for a ChatGPT-like AI product: auth.users (Supabase built-in), profiles, conversations, messages, embeddings, documents, usage_logs, model_configs. Migrations with Supabase CLI, seeding test data, database functions (stored procedures for embedding search), and triggers (auto-embed on document insert).

Row-Level Security (RLS) policies — users can only see their own data without any application-level filtering code, service role keys vs anon keys, database roles and permissions, Supabase Edge Functions for server-side logic (rate limiting, auth validation, calling AI APIs without exposing keys), and securing the AI system from prompt injection at the database level.

Every Database — At a Glance

When to use which database for AI applications. A decision reference to keep forever.

Build an AI Knowledge Base With Full RAG

Episode 30 (55 min): A complete, production-ready AI knowledge base using Supabase + pgvector + Python + Claude API. Upload documents, embed them, and ask questions — all powered by the database skills from Batch 05.

🗄️ AI Knowledge Base — Supabase + pgvector + RAG

A working AI system where you upload any document collection, and can then ask natural language questions answered with precise citations. Every database concept from this batch appears: schema design, SQL queries, indexes, RLS security, vector storage, HNSW index, semantic search, and a live LLM call — connected end-to-end.

📄 Data Layer

🔍 Search Pipeline

🤖 AI Integration

After Batch 05, Your Data Is Ready.

You now have Python, DSA, Math, and Databases. Batch 06 adds Networking — then Batches 07–11 are pure AI. Everything you've built leads here.

CS Origins

Complete

Python

Complete

DSA

Complete

Math for AI

Complete

Databases & SQL

You are here

Networking & APIs

HTTP, REST, WebSockets

Machine Learning

SQL features → ML models

Deep Learning

PyTorch + full data pipeline

Generative AI

Build on your RAG foundation

AI Products

Supabase is your backend

SELECT the Skills. Build the Product.

30 episodes. 6 chapters. One production RAG system. From your first SELECT query to a full AI knowledge base with semantic search, vector embeddings, and RLS security — free on YouTube under Codebitbybit × Tavro Studio.