YouTube captions in Postgres: preserve the revision your answer cited A developer outlined a Postgres schema for preserving YouTube transcript evidence behind timestamped citations, separating recordings, immutable transcript artifacts, and collection observations into distinct tables. The approach keys artifacts on video ID, returned language, and an evidence hash that covers both text and segment boundaries, rather than upserting transcript text on video ID conflict. The developer also recommended pinning ingestion inputs with a strict Zod validator and disabling Whisper fallback to keep the evidence contract dependable. 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. 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: python 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. 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: php 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. 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: js 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. 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: js 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. 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