Signing you in

A transcript is a wall of text. A knowledge base is a set of records you can query. This post is about the part in between: designing the data model that turns one into the other, so the result is searchable, checkable and mergeable across videos.

Before choosing a schema, list the questions a user will ask later:

Each question maps to a field. If a field has no question behind it, leave it out.

{ "name": "Supabase", "aliases": ["supabase"], "category": "database", "role": "backend and authentication", "mention": "used", "evidence": [ { "quote": "we use Supabase for auth and the database", "start": 761 } ], "official_url": "https://supabase.com" } Enter fullscreen mode Exit fullscreen mode Three design choices are worth explaining.

Store seconds, not strings. Keep the timestamp as a number of seconds. Format it for display later (00:12:41) and build deep links from it (?t=761). Strings like "12:41" break as soon as a video passes one hour.

Keep the quote. The quote is your audit trail. It lets a human confirm the claim in five seconds and lets your code reject records whose quote is not actually in the transcript.

Separate name and aliases. Videos say "Postgres", "PostgreSQL" and "Postgres DB". Normalise to one canonical name and keep the variants for matching.

Long transcripts are processed in chunks, so the same tool appears several times. Merge on a normalised key: lower-case the name, strip punctuation and common suffixes, then group. When merging, keep all evidence entries and the earliest start time, and take the most specific category. Rare conflicts, such as two categories for the same name, are worth logging instead of resolving silently.

Records that fail go to a rejected list with the reason. Looking at the rejected list is the fastest way to improve your prompt.

Once records are clean, the value comes from presentation. Sort by first appearance so the list follows the video. Make every timestamp a link. Group by category. Offer an export, because people will paste the result into their own notes.

Everything above is what we ended up building in VidScope, an experiment in turning technical YouTube videos into structured, timestamped lists of tools. The schema above is simplified, but the principles are the same: seconds not strings, quotes as evidence, and validation before display.

Why store timestamps as seconds? They are unambiguous, sortable and easy to turn into deep links and display formats.

How do I deduplicate tools across chunks? Normalise names (case, punctuation, suffixes) and merge records while keeping all evidence.

Why keep the quote? It is the proof: it lets a human check the claim quickly and lets code reject records not present in the transcript.

Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink.

For further actions, you may consider blocking this person and/or reporting abuse

DEV Community — A space to discuss and keep up software development and manage your software career

Built on Forem — the open source software that powers DEV and other inclusive communities.

We're a place where coders share, stay up-to-date and grow their careers.