Design a Database Schema for YouTube Transcript Data

Every YouTube transcript you pull from an API lands on your server as a JSON blob. What you do with that blob, whether you shove it into a single column or normalize it into queryable rows, determines whether your application scales or grinds to a halt.

This guide walks through a production-ready PostgreSQL schema for storing YouTube transcript JSON: how to model videos, segments, timestamps, and language variants cleanly, when to reach for JSONB versus normalized tables, and how to avoid the subtle bugs that appear when transcripts get updated or reprocessed.

The JSON Shape You Are Working With

Before designing any schema, read the data. The YouTube Transcriber API returns a consistent structure from its transcript endpoint:

{
  "video_id": "dQw4w9WgXcQ",
  "language": "en",
  "transcript": [
    {
      "text": "We're no strangers to love",
      "start": 18640,
      "duration": 3240
    },
    {
      "text": "You know the rules and so do I",
      "start": 21880,
      "duration": 2760
    }
  ],
  "metadata": {
    "title": "Rick Astley - Never Gonna Give You Up",
    "author_name": "RickAstleyVEVO",
    "author_url": "https://www.youtube.com/channel/UCuAXFkgsw1L7xaCfnd5JJOw",
    "thumbnail_url": "https://i.ytimg.com/vi/dQw4w9WgXcQ/hqdefault.jpg"
  }
}

Key details that shape your schema:

• start and duration are both in milliseconds, not seconds. A segment starting at 18640 begins at 18.64 seconds into the video.
• language is a BCP-47 code. The same video can yield multiple transcript objects in different languages.
• metadata is optional. You can pass send_metadata=false to omit it and reduce response size.
• There is no segment ID in the API response. Order is implied by array position.

JSONB Blobs vs. Normalized Segment Rows

This is the central decision. Both approaches have legitimate uses in this domain.

When JSONB makes sense

Storing the raw API response as a JSONB column is a good idea for one specific reason: auditability. If you ever need to reprocess transcripts, debug a mismatch, or replay ingestion, having the original response byte-for-byte is invaluable. JSONB also lets you query nested fields without joining tables, which is handy during early prototyping.

PostgreSQL's JSONB type stores data in a decomposed binary format, so it does not need to reparse the document on every read. You can index into specific keys with a GIN index. But for transcript data at any meaningful scale, raw JSONB falls apart quickly.

The problem: you cannot efficiently do range queries on start values stored inside a JSONB array. Updating a single segment's text rewrites the entire JSONB value. Full-text search across every segment in a large library requires scanning every row and unwrapping the array.

When normalized rows win

For querying, filtering, ranking, and joining, normalized rows with typed columns are consistently faster. A segment stored as a row with INTEGER columns for start_ms and duration_ms can be indexed with a B-tree or range type index. A TEXT column for segment text can carry a tsvector for full-text search. You can paginate, sort, and aggregate without touching JSON at all.

The trade-off is write complexity. You must parse the API response and insert multiple rows per video. For a 30-minute video with a segment every 3-4 seconds, that is roughly 500 to 600 rows.

The hybrid approach

Store the raw API response as JSONB for auditability. Normalize segments into typed rows for queries. This is the pattern most production systems settle on, and it is what the schema below implements.

The Schema

-- Core video record
CREATE TABLE videos (
  id            BIGSERIAL PRIMARY KEY,
  video_id      TEXT        NOT NULL,
  language      TEXT        NOT NULL,
  title         TEXT,
  author_name   TEXT,
  author_url    TEXT,
  thumbnail_url TEXT,
  caption_source TEXT,           -- "asr" for auto-generated, "manual" for human captions
  raw_response  JSONB,           -- original API response for auditability
  processed_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (video_id, language)
);

-- Normalized segment rows
CREATE TABLE transcript_segments (
  id             BIGSERIAL PRIMARY KEY,
  video_id       TEXT        NOT NULL,
  language       TEXT        NOT NULL,
  segment_index  INTEGER     NOT NULL,
  text           TEXT        NOT NULL,
  start_ms       INTEGER     NOT NULL,
  duration_ms    INTEGER     NOT NULL,
  UNIQUE (video_id, language, segment_index),
  FOREIGN KEY (video_id, language) REFERENCES videos (video_id, language)
    ON DELETE CASCADE
);

The UNIQUE (video_id, language) constraint on videos ensures you never accidentally store two conflicting transcript records for the same video and language. The cascade on delete means removing a video record cleans up all its segments automatically.

Indexing for Full-Text and Timestamp Range Queries

Two query patterns dominate transcript applications: searching for spoken phrases, and finding segments within a time window. Each needs a different index type.

Full-text search with GIN

Add a generated tsvector column to transcript_segments:

ALTER TABLE transcript_segments
  ADD COLUMN text_search tsvector
  GENERATED ALWAYS AS (to_tsvector('english', text)) STORED;

CREATE INDEX idx_segments_fts
  ON transcript_segments
  USING GIN (text_search);

The GENERATED ALWAYS AS ... STORED clause keeps the column current automatically on insert and update. No triggers needed. Querying looks like this:

SELECT
  video_id,
  language,
  text,
  start_ms,
  ts_rank(text_search, query) AS rank
FROM transcript_segments,
     to_tsquery('english', 'never & gonna & give') AS query
WHERE text_search @@ query
ORDER BY rank DESC
LIMIT 20;

For a deeper walkthrough of building search across transcript libraries, see Build a Full-Text Search Engine Over YouTube Transcripts.

Timestamp range queries with GiST

Convert start_ms and duration_ms into a PostgreSQL int4range for efficient overlap and containment queries:

ALTER TABLE transcript_segments
  ADD COLUMN time_range int4range
  GENERATED ALWAYS AS (
    int4range(start_ms, start_ms + duration_ms, '[)')
  ) STORED;

CREATE INDEX idx_segments_time
  ON transcript_segments
  USING GIST (time_range);

With this in place, finding all segments active during a specific window (say, 15 to 30 seconds in) is a single indexed query:

SELECT text, start_ms, duration_ms
FROM transcript_segments
WHERE video_id = 'dQw4w9WgXcQ'
  AND language = 'en'
  AND time_range && int4range(15000, 30000);

The && operator matches any segment whose range overlaps the given window. Without the GiST index, PostgreSQL scans every row in the table. With it, the planner uses the index tree to narrow candidates before evaluating the predicate.

Add a B-tree index on (video_id, language) for the common lookup pattern:

CREATE INDEX idx_segments_video_lang
  ON transcript_segments (video_id, language);

Storing Language Variants and Caption Source Metadata

Most YouTube videos have at least one auto-generated transcript and sometimes several manually provided ones. Your schema needs to represent both dimensions cleanly.

The UNIQUE (video_id, language) constraint on videos handles language variants. Each row is one transcript in one language for one video. If you fetch both English and Spanish transcripts for the same video, you get two rows in videos and two corresponding sets of rows in transcript_segments.

The caption_source column captures where the transcript came from:

• "asr": auto-generated by YouTube's speech recognition
• "manual": human-authored captions uploaded by the creator
• "translated": machine-translated from another language

This matters for downstream quality. Auto-generated captions often have no punctuation, run segments together, and occasionally produce phonetic errors. If your application feeds transcripts to an LLM or a search index, knowing the source lets you apply different pre-processing. For a detailed breakdown of the differences, see YouTube Captions API: Auto vs Manual Captions Explained.

Gotcha: Handling Reprocessed or Updated Transcripts

YouTube occasionally updates transcripts, and your ingestion pipeline may reprocess videos on a schedule or when it detects a change. Without a deliberate strategy, you will either silently overwrite correct data or accumulate duplicate rows.

The processed_at column on videos records when you last ingested a transcript. On reprocessing:

1. Upsert the videos row by (video_id, language) and update processed_at and raw_response.
2. Delete the existing segments for that (video_id, language) pair.
3. Insert the new segments.

-- Step 1: upsert the video record
INSERT INTO videos (video_id, language, title, author_name, caption_source, raw_response, processed_at)
VALUES ($1, $2, $3, $4, $5, $6, NOW())
ON CONFLICT (video_id, language)
DO UPDATE SET
  raw_response = EXCLUDED.raw_response,
  processed_at = EXCLUDED.processed_at,
  title        = EXCLUDED.title;

-- Step 2: remove stale segments
DELETE FROM transcript_segments
WHERE video_id = $1 AND language = $2;

-- Step 3: bulk insert new segments
INSERT INTO transcript_segments
  (video_id, language, segment_index, text, start_ms, duration_ms)
VALUES ...

Wrapping all three steps in a transaction ensures your database is never in a half-updated state. If the bulk insert fails, the delete rolls back too.

If you need to preserve history, add a version integer to videos and make the primary key (video_id, language, version). Query the latest version with a window function or a separate current_version column. This trades storage for full audit history.

One more edge case: YouTube can disable transcripts on a video after you have already stored them. Track a transcript_available boolean on videos and check it before serving cached data to avoid surfacing stale results.

Putting It All Together

Here is the complete schema with all columns and indexes in one place:

CREATE TABLE videos (
  id            BIGSERIAL PRIMARY KEY,
  video_id      TEXT        NOT NULL,
  language      TEXT        NOT NULL,
  title         TEXT,
  author_name   TEXT,
  author_url    TEXT,
  thumbnail_url TEXT,
  caption_source TEXT,
  raw_response  JSONB,
  processed_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (video_id, language)
);

CREATE TABLE transcript_segments (
  id             BIGSERIAL PRIMARY KEY,
  video_id       TEXT    NOT NULL,
  language       TEXT    NOT NULL,
  segment_index  INTEGER NOT NULL,
  text           TEXT    NOT NULL,
  start_ms       INTEGER NOT NULL,
  duration_ms    INTEGER NOT NULL,
  time_range     int4range GENERATED ALWAYS AS (
                   int4range(start_ms, start_ms + duration_ms, '[)')
                 ) STORED,
  text_search    tsvector GENERATED ALWAYS AS (
                   to_tsvector('english', text)
                 ) STORED,
  UNIQUE (video_id, language, segment_index),
  FOREIGN KEY (video_id, language)
    REFERENCES videos (video_id, language) ON DELETE CASCADE
);

CREATE INDEX idx_segments_video_lang
  ON transcript_segments (video_id, language);

CREATE INDEX idx_segments_fts
  ON transcript_segments USING GIN (text_search);

CREATE INDEX idx_segments_time
  ON transcript_segments USING GIST (time_range);

This schema handles the full lifecycle: ingesting the youtube transcript JSON response from the API, querying segments by text or timestamp, storing multiple language variants, and safely reprocessing updated transcripts without corrupting your data.

Next Step

The best way to validate your schema against real data is to fetch a few transcripts and inspect the JSON. The YouTube Transcriber API docs show the exact response shape for the transcript endpoint, including the millisecond timestamp format, optional metadata fields, and bulk request options. You start with 100 free credits and no credit card required, which is enough to ingest a handful of videos and test your insert and query paths end to end.