YouTube captions in Postgres: preserve the revision your answer cited
DEV Community

YouTube captions in Postgres: preserve the revision your answer cited

Your answer still cites the video. The transcript behind that citation has changed, and your upsert has helpfully deleted the version you embedded. You discover this while investigating an answer someone challenged. The recording is available. The evidence isn't.

A video ID identifies a recording, not a transcript revision. The Actor processes duplicate video IDs once, but language selection and collection paths determine which text comes back. Deduplicating collection doesn't preserve evidence.

Choose what your citation promises

For timestamped citations, preserve text and segment boundaries together. A text-only hash misses timing changes, even when those changes move the passage your answer points to.

Use Node with ES modules, apify-client, zod, and pg. Supply APIFY_TOKEN and your usual Postgres connection environment variables.

This Example input requests text and segments while disabling AI fallback:

import { ApifyClient } from 'apify-client';
import { z } from 'zod';
import pg from 'pg';
import { createHash } from 'node:crypto';

const client = new ApifyClient({ token: process.env.APIFY_TOKEN });
const pool = new pg.Pool();

const run_input = z.object({
  mode: z.literal('videos'),
  videoUrls: z.array(z.string().url()).min(1),
  language: z.literal('en'),
  outputFormat: z.literal('full'),
  useWhisper: z.literal(false),
  includeWordTimings: z.literal(false),
}).strict().parse({
  mode: 'videos',
  videoUrls: ['https://www.youtube.com/watch?v=jNQXAC9IVRw'],
  language: 'en',
  outputFormat: 'full',
  useWhisper: false,
  includeWordTimings: false,
});

The input validator deliberately accepts less than the source supports. Your ingestion job needs a dependable evidence contract, so allowing someone to switch it to subtitle output would create flexibility in precisely the wrong place.

Disabling fallback sacrifices coverage. If you enable it later, use maxWhisperMinutes to bound eligible video length and review the spending policy separately. That setting limits transcription eligibility. It isn't a monetary ceiling, and database deduplication doesn't refund collection work.

Give recordings, artifacts, and attempts separate keys

The dangerous statement is an upsert that replaces transcript text whenever video_id conflicts. It makes a recording look like a mutable document, although your citation needs the exact document it originally used. Keep these identities apart:

  • recording: video_id
    • +-- returned language + evidence hash --> immutable artifact
    • +-- run ID + dataset position --> observation
  • embedding / citation
Record Key Purpose
Video video_id Identify the recording
Artifact Video, returned language, evidence hash Preserve citation evidence
Observation Run ID, dataset position Preserve collection history

Requested language describes your intention. Returned language describes the artifact. Likewise, transcript_source describes how collection obtained the text. A transport change shouldn't manufacture a revision when the evidence remains identical, though you should retain that provenance for diagnosis.

Apply this schema before running the ingestion module:

CREATE TABLE ingest_runs (
  run_id text PRIMARY KEY,
  run_status text,
  ingestion_complete boolean NOT NULL DEFAULT false
);
CREATE TABLE videos (
  video_id text PRIMARY KEY
);
CREATE TABLE transcript_artifacts (
  video_id text NOT NULL REFERENCES videos,
  transcript_language text NOT NULL,
  evidence_hash text NOT NULL,
  full_text text NOT NULL,
  segments jsonb NOT NULL,
  PRIMARY KEY (video_id, transcript_language, evidence_hash)
);
CREATE TABLE collection_observations (
  run_id text NOT NULL REFERENCES ingest_runs,
  item_index bigint NOT NULL,
  video_id text REFERENCES videos,
  transcript_language text,
  evidence_hash text,
  flags jsonb NOT NULL,
  validation_errors jsonb NOT NULL,
  raw jsonb NOT NULL,
  PRIMARY KEY (run_id, item_index),
  FOREIGN KEY (video_id, transcript_language, evidence_hash) REFERENCES transcript_artifacts
);

Typed columns carry identity and the fields needed to retrieve evidence. Raw JSONB retains each observation, including requested language, source, translation status, and collection time. That split is intentional. Turning every metadata field into a column increases migration work without improving citation recovery; keeping everything inside JSONB makes the joins protecting that recovery harder to inspect. Promote provenance fields into indexed columns when your operational queries justify it. Until then, the original observation remains available without becoming part of artifact identity.

Validate the evidence, tolerate unrelated additions

A strict output schema makes harmless metadata additions fail ingestion. Unchecked passthrough does the opposite: it accepts change while leaving you unaware that anything moved. Use passthrough with explicit drift flags. Known evidence fields must still have the expected types, and a malformed record must retain its raw body instead of receiving helpful coercion.

These schemas distinguish collection outcomes from transcripts your indexer can accept:

const Segment = z.object({
  start: z.number().finite().nonnegative(),
  dur: z.number().finite().nonnegative(),
  end: z.number().finite().nonnegative(),
  text: z.string(),
}).passthrough().refine(s => s.end >= s.start);

const Envelope = z.object({
  video_id: z.string().min(1).optional(),
  requested_language: z.string().optional(),
  transcript_language: z.string().min(1).optional(),
  was_translated: z.boolean().optional(),
  is_auto_generated: z.boolean().optional(),
  transcript_source: z.enum(['library', 'android_player', 'playwright_dom', 'whisper']).optional(),
  full_text: z.string().optional(),
  segments: z.array(Segment).optional(),
  success: z.boolean(),
  transcript_available: z.boolean(),
  scrapedAt: z.string().datetime({ offset: true }),
  errorCode: z.string().optional(),
  retryable: z.boolean().optional(),
}).passthrough();

const Evidence = Envelope.extend({
  video_id: z.string().min(1),
  transcript_language: z.string().min(1),
  success: z.literal(true),
  transcript_available: z.literal(true),
  full_text: z.string().refine(text => text.trim().length > 0),
  segments: z.array(Segment).min(1),
});

const knownFields = new Set(`video_id title channel_name channel_id canonicalUrl sourceUrl published_date publishedAt duration_seconds views like_count category keywords description thumbnail channelHandle channelUrl channelThumbnail channelVerified subscriberCountText subscriberCount commentCountText commentCount isUnlisted is_family_safe is_live_content is_upcoming accessStatus wasLive liveStatus scheduledStartTime chapters heatmap captionTracks translationLanguages requested_language transcript_language transcript_language_name language_matched is_auto_generated was_translated transcript_source available_languages segments full_text wordTimings srt vtt ttml segment_count transcript_available success recordType error errorCode retryable language_probability inputUrl scrapedAt`.trim().split(/\s+/));

const segmentFields = new Set(['start', 'dur', 'end', 'text']);

This validates the fields the ingestion path depends on. It preserves other metadata without claiming that every nested structure has been checked. The distinction matters during an incident. โ€œStored successfullyโ€ means you retained the observation; โ€œaccepted evidenceโ€ means its text and timing passed the narrower contract your citations depend on. Missing optional metadata doesn't prevent acceptance. Missing transcript evidence does.

Hash exactly what your consumer uses

Canonicalization needs an explicit policy. Here, it preserves exact text and ordered segment values, with a version marker identifying the application's hashing convention. Don't trim whitespace or round timestamps unless your consumer applies those same transformations before storing and citing evidence. Otherwise, the hash promises equivalence that your users don't actually receive.

This function prepares an observation and attaches evidence only after validation:

function prepare(raw: unknown, item_index: number) {
  const row = {
    item_index,
    raw,
    video_id: null as string | null,
    transcript_language: null as string | null,
    evidence_hash: null as string | null,
    artifact: null as { full_text: string; segments: { start: number; dur: number; end: number; text: string }[] } | null,
    flags: [] as string[],
    validation_errors: [] as unknown[],
  };
  if (raw && typeof raw === 'object' && Object.keys(raw).some(key => !knownFields.has(key))) {
    row.flags.push('unknown fields');
  }
  const parsed = Envelope.safeParse(raw);
  if (!parsed.success) {
    row.flags.push('invalid shape');
    row.validation_errors = parsed.error.issues;
    return row;
  }
  const record = parsed.data;
  row.video_id = record.video_id ?? null;
  row.transcript_language = record.transcript_language ?? null;
  if (record.segments?.some(s => Object.keys(s).some(key => !segmentFields.has(key)))) row.flags.push('unknown segment fields');
  if (!record.success || !record.transcript_available) {
    row.flags.push('collection failed');
    return row;
  }
  const checked = Evidence.safeParse(record);
  if (!checked.success) {
    row.flags.push('invalid evidence');
    row.validation_errors = checked.error.issues;
    return row;
  }
  row.artifact = {
    full_text: checked.data.full_text,
    segments: checked.data.segments.map(({ start, dur, end, text }) => ({ start, dur, end, text })),
  };
  row.evidence_hash = createHash('sha256').update(JSON.stringify(['timed-transcript-v1', row.artifact.full_text, row.artifact.segments.map(s => [s.start, s.dur, s.end, s.text])])).digest('hex');
  return row;
}

Notice what stays outside the hash: scrape time, requested language, and transport provenance. Identical evidence collected again produces the same artifact key while gaining another observation. The version marker isn't decoration. Changing normalization rules requires a migration decision, because the resulting hashes belong to a different identity scheme. This contract fits segment-based citations. If you later cite individual word timings or require source-specific approvals, extend the contract before treating those properties as interchangeable.

Commit batches without replacing accepted history

Each page commits atomically. The database inserts video identities, inserts unseen artifacts, and records observations without updating existing transcript content. The transaction also takes an application-wide writer lock. That's conservative, but it prevents concurrent imports from overlooking each other's revisions when deciding whether new evidence differs from already stored material.

This writer uses batch statements rather than issuing an insert for every record:

async function persist(runId: string, rows: ReturnType<typeof prepare>[]) {
  const db = await pool.connect();
  const payload = JSON.stringify(rows);
  try {
    await db.query('BEGIN');
    await db.query('SELECT pg_advisory_xact_lock(9173)');
    await db.query(`INSERT INTO videos SELECT DISTINCT video_id FROM jsonb_to_recordset($1::jsonb) AS x(video_id text) WHERE video_id IS NOT NULL ON CONFLICT DO NOTHING`, [payload]);
    await db.query(`WITH incoming AS ( SELECT * FROM jsonb_to_recordset($2::jsonb) AS x( item_index bigint, video_id text, transcript_language text, evidence_hash text, artifact jsonb, flags jsonb, validation_errors jsonb, raw jsonb ) ), added AS ( INSERT INTO transcript_artifacts SELECT DISTINCT ON (video_id, transcript_language, evidence_hash) video_id, transcript_language, evidence_hash, artifact->>'full_text', artifact->'segments' FROM incoming WHERE evidence_hash IS NOT NULL ON CONFLICT DO NOTHING RETURNING video_id, transcript_language, evidence_hash ) INSERT INTO collection_observations SELECT $1, x.item_index, x.video_id, x.transcript_language, x.evidence_hash, x.flags || CASE WHEN EXISTS (SELECT 1 FROM added n WHERE (n.video_id, n.transcript_language, n.evidence_hash) = (x.video_id, x.transcript_language, x.evidence_hash)) AND EXISTS (SELECT 1 FROM transcript_artifacts old WHERE old.video_id = x.video_id AND old.transcript_language = x.transcript_language AND old.evidence_hash <> x.evidence_hash) THEN jsonb_build_array('changed evidence') ELSE '[]'::jsonb END, x.validation_errors, COALESCE(x.raw, 'null'::jsonb) FROM incoming x ON CONFLICT (run_id, item_index) DO NOTHING`, [runId, payload]);
    await db.query('COMMIT');
  } catch (error) {
    await db.query('ROLLBACK');
    throw error;
  } finally {
    db.release();
  }
}

The revision comparison sees artifacts present before that statement. A newly inserted artifact receives a change flag when its language already has different evidence; replaying an existing artifact doesn't manufacture another revision alert. The flag strings belong to your application. They describe ingestion decisions, not fields returned by the source.

The global lock limits write throughput. At larger scale, assign ownership by video or use consistently ordered per-video locks, preserving the requirement that revision detection and artifact insertion agree about prior state. Store the artifact's composite key with every embedding and citation. Looking up the newest transcript at answer time recreates the original failure with better table names.

Resume ingestion before starting another collection A terminal run gives dataset positions a stable role in obse

Read on DEV Community ↗ ← Back to News

Comments

No comments yet. Start the discussion.